SQL Execute Task +table input parameter? - 10-01-2007 , 04:54 AM
Is it possible to pass a result of one select from SQL Execute Task to
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.
Re: SQL Execute Task +table input parameter? - 10-02-2007 , 10:00 AM
On Oct 1, 10:54 am, christof <christof_mt... (AT) SPAMwp (DOT) pl> wrote:
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.ToString() + ", " +
The Expression in your SET will then set the SQLStatementSource
"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.