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.
Sherry's BI Corner 
Perfect, just what I neeeded.
LikeLike
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.
LikeLike
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
LikeLike
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
——————————
LikeLike
My new post might be able to help you.
Good luck,
Sherry
LikeLike
Hi
Just a comment on passing a date variable from SSIS to SQL if your local time is not MM/DD/YY eg UK, Australia, etc.
I found that when entering the date 01-07-2005 (1 july 2005) as a variable to be passed to SQL SSIS will display the varible as 01-07-2005 BUT pass the date to SQL as 07-01-2005 (7 January 2005).
By entering the variable as 2005-07-01 the variable is passed correctly.
Weirdly when you look at this date in the SSIS variable it is displayed as 01-07-2005, exactly the same as the “incorrect” date was displayed.
LikeLike
HI
I am a newbie to SSIS.. it is good article. but my User variable is not passing from Control Flow to data flow . It is giving following error ..
Error: Variable “User::XXXX” does not contain a valid data object
Am I doing something wrong or am missing something?
Thanks in advance!!
Roma
LikeLike
I have implemented this on my data flow using an OLE DB Source.
On the OLE DB Source, I used the SQL Command then on the SQL command text, I ahve this
EXEC [dbo].[mystoredproc_queryperson] ?
where ? is an input parameter @PersonID
On the Parameters tab (set Query Parameters, I changed the named of the Parameter0 as @PersonID then create a user variable: User::PersonID
When I run this using debug mode, everything is working fine. But when I run it using a stored procedure with xp_cmdshell dtexec command, it is not recognizing the input parameter.
e.g:
DTEXEC /File “E:\MySSISFolder\MyPackage.dtsx” /Set \Package.Variables[User::PersonID].Properties[Value];”1″
where 1 is the PersonID
Am I missing anyting?
Jose
LikeLike
Hi
Please try
EXEC [dbo].[mystoredproc_queryperson]
@PersonID = ?
And In Parameters tab Set
Parameters -> @PersonID
Variables-> User::PersonID
Hope this helps you !!
Thanks
Roma
LikeLike
Hi, that’s fine when your are usign a simple condition ie select * from xxx where cond=?. But it doesn’t work when you’re using an inner nest or a more difficult condition. Ie select * from xxx1 inner join (select max(i) from xxx where cond>?) as u on u.i=xx1.i. Can I manage this with ole? It works only with an .net connection! Thank you
LikeLike
HI sherry, what if I want to pass a variable into the select statement instead of the Where clause? Seems your approach only works if the paramenters are on the where clause.
thanks
LikeLike
I hope your question has already been answered, but in case someone else has the same question…
Passing a variable into the SELECT statement is not possible. I would recommend using a ‘Derived Column’ task to pass variables as columns. This will have the same effect of using a variable in the SELECT list.
LikeLike
hey i have an ole db component thats calling a stored procedure that has the output parameter as of type nvarchar(max)
it returns an xml value
i have been trying to map it to a DT_NTEX column in the ole db comand but it never returns a value
i tried with nvarchar(50) and that worked so im assuming its the DT_NTEX issue
(cant really use nvarchar(50) since its an xml column and will get concatenated)
is there a way to solve this ?
appreciate any help
LikeLike
“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.”
That’s exactly what happened to me. The editor handled a simple query, but with my initial query, it didn’t want to cooperate with the variables 🙂
Funny and annoying bug.
LikeLike