sql server dts: Hello,
Is it possible to pass a result of one select from SQL Execute Task to
another one?
I have a scenario (lets name SQL Execute Task SET to simplify):
1) SET1 is a long duration SELECT and executes a function that returns
me a one column (COL1) table
2) I use the result to foreach containter
Now i need to use a SET2 to make a query like:
SELECT sth FROM sometable WHERE sth NOT IN (.. result from SET)

I don't know how to do that - there is no input parameter that suits.
I've tried to put a Data Flow Task and to populate the Recordset
Destination to a variable Table [Object], but how then can i handle that
as a table or that result set.

[quoted text, click to view]

Hi Christof,

You will need to generate the NOT IN clause as a string variable of
comma seperated values in a script task and then set your SELECT
statement dynamically using an Expression. Assuming your recordset
values are assigned in your ForEach loop to a variable called "Col1"
and you have created a string variable called sNotIn, your script will
look something like:

DTS.Variables("sNotIn").Value =
DTS.Variables("sNotIn").Value.ToString() + ", " +
Dts.Variables("Col1").Value.ToString()

The Expression in your SET will then set the SQLStatementSource
property to:
"SELECT sth FROM sometable WHERE sth NOT IN (" + @[User::sNotIn] + ")"

Remember that you will need to loop through ALL the values in your
recordset before you will have a complete comma seperated list of the
values. This means that either the 2nd SET must probably be outside
your Foreach loop.

Good Luck!
J