Search code examples
sql-serverinsertssisoledbinsert-update

SSIS OLEDB Command transformation (Insert if not exists)

Ok so according to Microsoft docs the OLE DB Command Transformation in SSIS does this

The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.

So I want to write some SQL to Insert rows in one of my tables only IF the record doesn't exists

So I tried this but the controls keeps complaining of bad sintaxys

IF NOT EXISTS
(SELECT * FROM M_Employee_Login WHERE 
           Column1=?
           AND Column2=?
           AND Column3=?)

INSERT INTO [M_Employee_Login]
           ([Column1]
           ,[Column2]
           ,[Column3])
     VALUES
           (?,?,?)

However if I remove the IF NOT EXISTS section (leaving the insert only) the controls says may code is Ok, what am I doing wrong.

Is there an easier solution?

Update: BTW My source is a Flat File (csv file)

Update since answer: Just to let people know. I ended up using the OLE DB Command Transformation like I planned cause is better than the OLE DB Destination for this operation. The difference is that I did used the Lookup Component to filter all the already existent records (like the answer suggested). Then use the OLE DB Command Transformation with the Insert SQL that I had in the question and it worked as expected. Hope it helps

Solution

  • OLEDB Command object is not the same as the OLE DB Destination

    Rather than doing it as you describe, instead use a Lookup Component. Your data flow becomes Flat File Source -> Lookup Component -> OLE DB Destination

    In your lookup, you will write the query SELECT Column1, Column2, Column3 FROM M_Employee_Login and configure it such that it will redirect no match entities to the stream instead of failure (depending on your version 2005 vs not 2005) this will be the default.

    After the lookup, the output of No Match will contain the values that didn't find a corresponding match in the target table.

    Finally, configure your OLEDB Destination to perform the fast load option.