Continue from SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow.

Step 2: Create a data flow step, into which we are going to pass a variable

Step 3: Create an OLE DB Source, Destination and a Data Conversion

Step 4: In the OLE DB Source Editor, write a SQL query.

Notice the WHERE clause, WHERE M.TIME_DAY_GEN_ID = cast( ? as date ).

The question mark ? indicates that we need a parameter for this query.

Attention: when writing SQL query in this editor, always start with a simple query. The editor is not very sophisticated SQL editor. Complex SQL queries will confuse the editor.

Step 5: Click the Parameters… button.

In the Set Query Parameters windows, add a parameter by setting Parameter0 under Parameters, and User::vLoopDate under Variables.

Continue to finish the Column mappings.

We are done now.

If you open the Advance Editor for the OLE DB Source, you will see this in the Component properties tab.

Notice the ParameterMapping and the SQLCommand properties.