How to use the Merge task in SSIS

I have an ODBC connection that I am using a SQL command to execute the query. It reads from an ORD_HDR table and joins to an ORD_DTL table. It uses this at the source and I have to put this data into a Fact table as the destination. I was told I should use the Merge statement to see if I should use an Insert or Update to the Fact table. I want to test this but Im not sure how to use the Merge so can someone help me on this please? Do I use the query that joins the 2 tables or do I make 2 connections, one for the ORD_HDR and one for the ORD_DTL and then the merge to see if I should Update or Insert? I want to do something like an Upsert. If the record exists then do an Update and if it doesnt then an Insert?
Who is Participating?
Rainer JeschorConnect With a Mentor Commented:
I think the expert meant in this case not the SSIS merge component but the T-SQL MERGE statement.
Normally in a DW process, you first grab the rows from the source system and write it into a staging table on your SQL server.
Depending on the environment with or without additional cleanup, trimming, lookups etc.
Then you can use the T-SQL MERGE solely on the SQL server to UPSERT your imported data.

T-SQL Merge in SSIS

T-SQL Merge statement:

Alpesh PatelAssistant ConsultantCommented:

You need to join both source and destination tables using merge join task and apply appropriate join.

You can check joined column to identify new, update or deleted row.

See below article for whole working example.

I have an SSIS package that uses merge.
Have a look at the attached pic.

The secret is to sort the 2 separate data items first, using the same key.
The next part is the merge and what to do to each piece of data from the 2 sources.

The destination field maybe "City".
Data 1 may have the field City in it, but Data 2 may not have a city. So, you setup the merge to put Data 1 City into City field and ignore for Data 2.

I think when you put the merge task into your package the data connections become apparent.

Hope this helps

jknj72Author Commented:
I only have 1 source. I put the data from my ODBC connection into a SQL table and then I need to get into my Fact table but I have about 20 lookups along the way. Since I really only have the 1 source table should I be using the Merge? I really could use some advice on this. The initial job was setup with the ODBC connection as the source and that is connected to Lookups and then at the end it has a Slowly Changing Dimension to Insert or Update. Take a look at the pic I attached and let me know if anyone has any suggestions as to what the best way to do this would be. The source is 150,000 rows and the Fact table has over 6 million rows. Please see pic attached of orig Data flow...Thanks everyone
jknj72Author Commented:
sorry took so long
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.