SSIS source task for ADO.net with parameter

Question

I am using an ADO.NET source to transfer data from an ODBC server to my SQL. I used the import and export wizard to create my data flow task.

I need to add new entries that have the GPMTADDT> field (yesterday date) for example, if today 20121002 the following query works fine:

SELECT PARTICIP.* FROM PARTICIP WHERE GPMTADDT > 20121001 

I tried:

 SELECT PARTICIP.* FROM PARTICIP WHERE GPMTADDT > Format(now(),"yyyymmdd") 

But this will not work, the server treats the "format" as a column. I tried to create @date variable

 SELECT PARTICIP.* FROM PARTICIP WHERE GPMTADDT > @date 

again, the server rejected the "@".

 Also tried: SELECT PARTICIP.* FROM PARTICIP WHERE GPMTADDT > (SELECT MAX PARTICIP.GPMTADDT FROM PARTICIP) 

I'm sure I missed something simple, help would be greatly appreciated.

Thanks!

Answer 1

You tried:

 SELECT PARTICIP.* FROM PARTICIP WHERE GPMTADDT > DATEADD(dd, -1, GETDATE()) 

Answer 2

You did not indicate what your data source is, but there are several errors in the SQL syntax you tried. for example, the first request should have single quotes around the date, the last should put the argument MAX() in parentheses, etc.

But to try to answer your basic question, ADO.NET sources do not support parameters, unlike OLE DB. The following options are possible:

• Do not use a parameter if you can generate a value in the source itself; this is what you are trying to do in your last example and what Jeff suggested
• Use the SSIS expression to set the SqlCommand property of the connection object along with the SSIS variable (as described here )
• As an alternative to # 2, use the Script task to create a complete SELECT query, assign it to the SSIS variable, and then use this variable as the SqlCommand value

Personally, I would say that option 1 is the simplest, but, of course, not always possible if the variable is not available from the source system. 2 and 3 are variations of the same solution, but I prefer 3 because I find it easier to write Script than working with expressions.