THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

The current state of a MERGE Destination for SSIS

Hugo Tap asked me on Twitter earlier today whether or not there existed a SSIS Dataflow Destination component that enabled one to MERGE data into a table rather than INSERT it. Its a common request so I thought it might be useful to summarise the current state of play as regards a MERGE destination for SSIS.

Firstly, there is no MERGE destination component in the box; that is, when you install SSIS no MERGE Destination will be available. That being said the SSIS team have made available a MERGE destination component via Codeplex which you can get from http://sqlsrvintegrationsrv.codeplex.com/releases/view/19048.

image

I have never used it so cannot vouch for its usefulness although judging by some of the reviews you might not want to set your expectations too high. Your mileage may vary.

 

In the past it has occurred to me that a built-in way to provide MERGE from the SSIS pipeline would be highly valuable. I assume that this would have to be provided by the database into which you were merging hence in March 2010 I submitted the following two requests to Connect:

If you think these would be useful feel free to vote them up and add a comment.

Lastly, this one is nothing to do with SSIS but if you want to perform a minimally logged MERGE using T-SQL Sunil Agarwal has explained how at Minimal logging and MERGE statement.

@Jamiet

Published Friday, December 07, 2012 12:18 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kevin said:

I tried this out and it seemed rather sketchy. It's cumbersome to set up and very prone to errors that lead to the component no longer being in a state where you can edit it.

However - it did lead me to a much nicer solution of just writing the procedures to use table valued parameters, combined with a recordset and a script task I now have some nice working merge functionality from SSIS.

December 7, 2012 5:03 PM
 

Michael said:

Kevin,

Can you blog on the nicer solution?  Or Jamie can you pick up that response and give us a demo of that?

March 28, 2013 12:06 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement