After I created the Batch Destination for my presentation at the MS BI Conference, I was talking with Matt Masson, who let me know that they’d been working on a MERGE Destination sample component, and would hopefully be releasing it soon. It’s out now on the CodePlex site for the Integration Services team samples. I’ve taken a look at it, and it is very nicely done.
What’s the difference between the two? The MERGE Destination has a much nicer interface, that lets you graphically build a MERGE statement. And, of course it uses the MERGE statement to perform updates and inserts in a single operation. This is nice, but limits you to using a SQL Server 2008 database as a destination. The Batch Destination executes any SQL command you give it, so it can be used on SQL Server 2005 or 2008.
I haven’t done a performance comparison between the two, so I’m not positive which one is faster. However, the MERGE Destination stores the working data in memory, so it should be more efficient at loading the data. This does require the MERGE Destination to create a type and a stored procedure in the destination database, so the appropriate permissions need to be provided. The Batch Destination uses the ADO.NET Bulk Copy functionality, so the data is persisted in a working or temporary table in the target database, and cleaned up afterward, which is likely to be slower. However, you don’t need any special permissions to create a temporary table.
This isn’t intended to be a “this one is better than that one”. I can see using either or both as circumstances dictate. I just think it’s nice to have options.