Pass a variable to a DataReader in a DataFlow task

How can I pass a variable to a DataReader in a DataFlow task?

My SqlCommand for the DataReader is:
SELECT CustName, CustCode FROM Customers WHERE CustCode = '?'

The DataFlow task is nested in a ForEach loop. I confirmed that the variable is changing with each loop by using a ScriptTaks and a message box. However, the DataReader SqlCommand does not seem to be updating.

You need to put the query into a variable, let's say @.myquery.

@.myquery would use an expression to make it dynamic on each iteration. Then you have to assign @.myquery variable to the data reader via expression. For doing that, go to the control flow; select the data flow task go to properties; select expression and add an expression to SQLCommand proepty. The proeprty should be just: @.myquery.

|||

This article by Jamie Thomson gave me more detail to accomplish what Rafael described:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

Note: the SQLCommand property of the DataReader gets renamed to [Name of DataReader Source]

|||

Well,

That article applies for OLE DB source component; for a data reader is slightly different.

Good that it worked for you!