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
Solved

How to use the Merge task in SSIS

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 33
First Max value 3 30
query linked sql table field from access 4 22
question about results where i dont have a match 3 23
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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