SSIS – Pass a variable to a OLE DB Source in a Data Flow
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.
Perfect, just what I neeeded.
Hi,
How would I go about passing a variable to an ADO.Net Source, for example Teradata. I would like to query teradata using a variable to return a specified query result.
Been struggling to get this work, help would be appreciated.
Hi,
I’ve answered your question in a new blog,
SSIS #101 – Use expressions to pass parameters to ADO.NET Source
Hope it helps.
Sherry
getting below error,
……where s.iso_country = cast( ? as varchar)
Any sugesstions?
TITLE: Microsoft Visual Studio
——————————
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the “SQL command from variable” access mode, in which the entire SQL command is stored in a variable.
——————————
ADDITIONAL INFORMATION:
Syntax error, permission violation, or other nonspecific error (Microsoft SQL Server Native Client 10.0)
——————————
BUTTONS:
OK
——————————
My new post might be able to help you.
http://bisherryli.wordpress.com/2012/01/27/ssis-107-parameterized-query-in-ole-db-data-source-parameter-information-cannot-be-derived-from-sql-statements-with-sub-select-queries/
Good luck,
Sherry