Solved

How to use the Merge task in SSIS

Posted on 2015-02-08
5
66 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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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