Solved

How to use the Merge task in SSIS

Posted on 2015-02-08
5
61 Views
Last Modified: 2016-02-15
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?
0
Comment
Question by:jknj72
5 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 40597722
Hi,
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.
Concept:
http://microsoft-ssis.blogspot.de/2013/02/t-sql-merge-in-ssis-as-scd-alternative.html

T-SQL Merge in SSIS
https://msdn.microsoft.com/en-us/library/cc280522(v=sql.110).aspx

T-SQL Merge statement:
https://msdn.microsoft.com/en-us/library/bb510625(v=sql.110).aspx

HTH
Rainer
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 40597742
Hi,

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.

https://itssmee.wordpress.com/2010/10/03/ssis-insert-and-update-rows-in-a-table-based-on-the-contents-of-a-excel-file/
0
 
LVL 12

Expert Comment

by:Tony303
ID: 40597746
Hi,

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.

EG,
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

Thanks
Tony
Merge.jpg
0
 

Author Comment

by:jknj72
ID: 40599301
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
FactSalesOrder.jpg
0
 

Author Closing Comment

by:jknj72
ID: 40805117
sorry took so long
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now