How to impletement this query in SSIS package

Is there component in SSIS Package that I can use SQL Statement to update input dataset column based on a join query with a table?

update <input dataset> set column01 = -1
where column01 not in (select column from dbo.columnlist)
and column01 <> -1

Perhaps a combination of derived columns, conditional statements, and merge joins (perhaps).|||

Hi Phil,

if I can't not write SQL script to impletement this, I have to use condition split (for column01<>-1 ) -> Sort->Merge Join(Join dbo.columnlist table )->Condition Split (column from dbo.columnlist is null), derived column (update column01 with -1) -> Union All.

it looks so complicated.

do you know is there any component that we can write script to update the input column set? I know we can use VBScript component to do this , but I'm wondering if we can use SQL Script.

|||I think you should perform a lookup transformation against "select column from dbo.columnlist".

Using the lookup error output, you can add a derived column that sets column01 to -1. Then, use a union all to join the normal lookup output with its error output.

Don't worry about the "and column01 <> -1" statement. It is worthless.|||

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

I tried with 'enable memeory restriction' and modify the SQL Statement , replacing the '=' with '<>', but it prompts error when I debug, saying 'no match rows', but actually I do have match rows.

|||

Jeff_LIU wrote:

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

Right, which is why I said to use the error output. For every input row that isn't in the lookup, it'll go down the error output (the red arrow).