SSIS (in SQL Server 2012): Upsert in Lookup component

Question

I have > 10 packages that need to update/insert in the dataflow. I am able to do it by:

• Lookup => Match output branch => OLE DB Command.
• Lookup => No Match output branch => OLE DB Destination.

(http://www.rad.pasfu.com/index.php?/archives/46-SSIS-Upsert-With-Lookup-Transform.html) (http://jahaines.blogspot.com/2009/09/sss-performing-upsert.html)

However, I was wondering if there is some way I can use the "Merge" statement in Lookup (or in any other) component such that I can do something like:

MERGE [DBPrac].[dbo].[TargetTable] AS tt
USING [SourceTable] AS st ON tt.Id = st.Id

WHEN MATCHED THEN --* Update the records, if record found based on Id.
   UPDATE 
      SET tt.SSN = st.SSN 
          ,tt.FirstName = st.FirstName
          ,tt.MiddleName = st.MiddleName
          ,tt.LastName = st.LastName
          ,tt.Gender = st.Gender
          ,tt.DateOfBirth = st.DateOfBirth
          ,tt.Email = st.Email
          ,tt.Phone = st.Phone
          ,tt.Comment = st.Comment

WHEN NOT MATCHED BY TARGET THEN --* Insert from source to target.
    INSERT (Id, SSN, FirstName, MiddleName, LastName, Gender, DateOfBirth, Email, Phone, Comment)
    VALUES (st.Id, st.SSN, st.FirstName, st.MiddleName, st.LastName, st.Gender, st.DateOfBirth, st.Email, st.Phone, st.Comment)
;

SELECT @@ROWCOUNT;

SET IDENTITY_INSERT [dbo].[TargetTable] OFF
GO

So far I tried:

• In Lookup component's "Advanced" pane in "Custom query", I tried to use the above query, but stumbled upon the "SourceTable". Don't know how to get hold of input recordset in the "Custom query" (Don't know if it is even possible).

Any help and/or pointer would be great.

Answer 1

Yes you can use MERGE but you need to load your data into a staging table. This is the 'ELT' method - extract, load (into database), transform, as opposed to the 'ETL' method - extract, transform (in package), load (into database)

I usually find the ELT method faster and more maintainable, if you don't mind working with SQL scripts. Certainly a single bulk update is faster than the row by row update that occurs in SSIS