September 6, 2013 at 4:45 am
Hello,
Can you please tell me how i can accomplish the following:
I have a list of IDS which i extract from SQL Server 2008 table , which i want to pass in a query (it's a big monster with quite a lot of inner, outer joins). I'll use the query to extract the records from Oracle Server and save back to Sql server.
I'm using Ole Db source.
Please let me know if you need more details to provide a possible solution
Thanks a lot
Vik
September 6, 2013 at 5:02 am
You can create a string variable in SSIS containing your SQL statement. Just add the IDs you extracted to this variable using an expression.
In the OLE DB Source, use the string variable as a source for the SQL statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 6, 2013 at 10:20 am
Thanks Koen,
This is what i did until now:
Data Flow Task (DFT1) : Used Ole Db Source to extract all the Ids and Save them in a Recordset destination. Used variableZ as Object here
Link the DFT1 to a Foreach Loop Container. "Foreach Ado Enumeration"
Enumeration mode: ROws in the first table.
Variable mapping : VariableY= 0 ( It's defined as string)
Inside the foreach loop i have another DFT2 and Ole DB Source data Access mode = SQl command from variable. I built the expression to grab the SQL QUery + The ID coming from the FOreach loop , one by one.
The problem is that the last output result is exporting only 1 record.
Note that i can have more than 10000 Ids to pass in the query.
I can't pass all of then in the query at once, since in oracle i can put only 999 Ids.
And the ids should be in quotes like
WHERE ID IN ( 'ID1','ID2','ID3','IDnth')
Thks advance,
Vik
September 9, 2013 at 3:20 am
Wouldn't it be faster to transfer everything to SQL Server and then delete everything you don't need?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply