[SSIS] BULK MERGE Destination - by Jamie Thomson

Status :

Description

In ConnectID=540038 (https://connect.microsoft.com/SQLServer/feedback/details/540038/bulk-merge) I have requested that a BULK MERGE feature be added to the SQL Server Database Engine.
The submission you are reading right now is a request to provide a destination component in SSIS that could leverage this database feature were it to be implemented.
===========================================================
We currently have a BULK INSERT operation which is very very useful when inserting data into SQL Server from the SSIS Pipeline.
A common requirement when processing data into a destination from the SSIS pipeline is to work out whether the row already exists in the destinaiton or not. If it does then UPDATE it, otherwise INSERT it. However, this is a very expensive series of operations and it would be a lot better if we could simply push data into a destination component and let the database engine take care of the comparison for us.
In other words, we need a BULK MERGE operation in the database. It would carry out the same basic operation as the existing MERGE statement but it would do it quickly for large datasets in exactly the same way as BULK INSERT does for insertions today.
By way of extra explanation, this is intended to provide an alternative option to the traditional:
source-->Lookup-->(via NoMatch output)-->OLE DB Destination
|
------->(via Matched output)-->OLE DB Command
pattern that we see so very very often in SSIS and which has a number of potential bottlenecks (not least high memory usage by the Lookup cache).
N.B. I have been conversing with Sunil Agarwal in the DB engine team about this so he is aware of it. Please involve him when triaging this.

Assign To

Thank you for contacting us. After analyzing this issue and prioritizing it in the context of our current development plans, we have found regretfully that it does not meet the bar to be included in an upcoming product release. If you feel that you could provide us with more data to change that judgment, we welcome any further information. Your feedback continues to be invaluable to us and we take all your communication seriously. Please continue to contact us with any questions, suggestions and issues as they arise.

 The SSIS Team

Posted by Jamie Thomson on 3/23/2010 at 7:41 AM

Please post comments and use cases if you vote this up. if you vote down please say why.