Wednesday, May 16, 2007

SSIS packages and Variables

Today, I spent most of the day trying to figure out why the following:
(this is inside of an SSIS package, not just a query)

select * from foo where foodate >= getdate()- ?
where the variable for the data is set to 2.

And the package really did not like when I tried to do the following:
select * from foo where foodate>= dateadd(d,?,getdate) and used a -2 for the value. That time it threw all kinds of errors.

If I hard coded the number in, it returned the data that I wanted, but if I left it as a package variable nothing would work.

In the end I had to create a stored procedure passing the variable as a parameter in the SSIS package, and then things worked fine.

Oh well.

Note: Hopefully soon, I'll get several posts about how to from start to finish get a SSIS package running in Sql Server 2005.

No comments: