November 17, 2011 at 7:34 am
Can you pass a variable to the data reader source sql. In my case I have a list of Customer # and am going again a very large database (in teradata) and want to reduce the time spent retrieveing the data. I can use a merge join but that doesn't reduce the extract time. I'd like to pass a string with the Cust #'s.
November 18, 2011 at 6:57 am
You can pass variables as parameters to the OLE DB Source SQL query:
http://msdn.microsoft.com/en-us/library/ms141696.aspx
However, this can be somewhat buggy from time to time. Especially if you are using other sources than SQL Server.
I would advice to create a variable of type string that contains your SQL statement, put an expression on that variable and create your statement dynamically using your variables.
You can use the result as the input for your SQL query in the OLE DB source.
An example:
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/22/ssis-design-pattern-dynamic-sql.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2011 at 6:10 am
Thanks for the reply. I am going against Teradata and using a merge join with sql server table. I created 3 variables for the Teradata query due to the length of the query and the 4000 character restriction. I used a script to create the variables.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply