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.
N.B. I have been conversing with Sunil Agarwal about this so he is aware of it. Please involve him when triaging this.
Oh, and 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).
BULK MERGE would also be very useful for bcp I'm sure.
I have added a matching connect item (https://connect.microsoft.com/SQLServer/feedback/details/542924/ssis-bulk-merge-destination) requesting a destination component in SSIS that would leverage BULK MERGE feature in the DB engine.