How to pass ado object to an in clause of a sql command in SSIS

Question

Details (this is specific to SSIS) : There are two database servers : A & B I have to fetch list of few values from database A (like 1,2,3,4,5 in one column) and pass it in the "IN" clause to a command that will run on database B (like Select something from B.Table where column in (1,2,3,4,5)).

The "Execute SQl task" result set is mapped to an object variable X, which cannot be passed directly as a parameter to the second command.

What are the possible solutions to this?

You can join them, just link the databases: http://stackoverflow.com/a/5145685/284240 — Tim Schmelter, May 23 '16 at 08:51
But in SSIS, i believe one execution task can have only one connection to a database. — Jim, May 23 '16 at 08:57
In the join use four part naming `...FROM LinkedServerName.Database.Schema.Table ...`. — David Rushton, May 23 '16 at 09:49

score 0 · Accepted Answer · answered May 23 '16 at 13:19

0

One possible solution is to use a Script Task to iterate through the Object Variable and build a SQL String to be used in the second Execute SQL task.

answered May 23 '16 at 13:19

Tab Alleman

31,483
7
36
52

In that case i assume you are suggesting dynamic sequel because in the in-clause" normal string parameter is taken as a string (one value) rather than being taken as a comma separated int values. Is it possible to use dynamic sequel in a task? – Jim May 23 '16 at 16:06
Not exactly dynamic sql. Populate a package variable with your entire SQL String, dynamically building the IN clause in the script task. Then in the next Execute SQL task, choose the SQL From Variable option, and use your SQL String variable. – Tab Alleman May 23 '16 at 17:08
That solved the issue. Although i did not want to touch the sql command that was supposed to run on server B, rather just substituting it somehow, but anyways it helped move further. Thank You. :) – Jim May 24 '16 at 06:16

How to pass ado object to an in clause of a sql command in SSIS

1 Answers1