Sql-server – How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS

sql serversql-server-2008sql-server-2008-r2ssist-sql

I was just wondering, in MERGE in T-SQL, you can output the OUTPUT $action, Inserted.* and Deleted.* records. I want to ask how can I get OUTPUT $action, Inserted.*, Deleted.* records in SSIS?

Best Answer

You can't, ish, is the short answer.

You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component.

I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether cutting down a sequoia tree with the miniature saw is realistic is another matter.

OLE DB Destination is that, a destination. It doesn't push rows out, it only consumes them. Yes, technically it can push rows but that's only through the error output path which still won't solve the problem of getting access to the OUTPUT virtual tables.

OLE DB Command sounds promising. It can add additional columns to your data flow. The big, BIG, BIG, downside is that it is not a set based component. It's going to operate on each individual row that flows through. If there's tens, hundreds, maybe thousands of rows, this singleton behaviour probably isn't going to kill your systems. MERGEing lakhs+ of data is probably going to result in you having a bad time.

If you're deadset on this approach, recognizing the failings with MERGE, the way to make it SSISey, would be to have two data flow components.

The first writes all of your data to a destination table. Take your existing DF and write all the data to a new table.

The second, new Data Flow, uses your MERGE statement with the accompanying virtual tables directed as needed. I talked about a delete first pattern which used the OUTPUT table over on that SO question so the MERGE shouldn't be any different.