?
Solved

How to use the Merge task in SSIS

Posted on 2015-02-08
5
Medium Priority
?
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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
Suggested Courses

762 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