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.