CODEWITHSUNDEEP
sqlsql-serverssisssis-2012
SUpreti
SUpreti

Reputation: 105

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

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

(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:

Any help and/or pointer would be great.

Upvotes: 1

Views: 1564

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

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

Upvotes: 1

Eugene Niemand
Eugene Niemand

Reputation: 729

If I understand your question correctly, just execute the Merge statement using an Execute SQL task. Then you dont need any Lookups. We use the same strategy for our warehouse's final load from staging.

Upvotes: 0

Related Questions