Sql-server – SSIS data flow to update source table rows after copying to destination

sql serverssis

I have a simple data flow that copies a subset of data from a source table on an internal database to a table on web-facing database.

If there is a problem, the error is outputted to an errors table.

That's all fine.

In the source table there is a bit column for SSIS_TRANSFERRED that I wish to set to 1 when the copy process completes. However, I'm unsure how to approach this.

My instinct is to craft an SQL Statement that runs against each Unique ID for every row successfully transferred as part of that package – is there a simple approach to this (i.e. as part of the data flow) or do I need to create a new Control Flow with OLE DB Command that queries the web-facing table and marks the corresponding internal rows as 'transferred' accordingly?

enter image description here

Best Answer

If you want to keep all the components within the current Data Flow Task, then you could add a Multicast within this Data Flow Task, with one output to the destination and the other output to an OLE DB Command that updates the source records based on the rows transferred to the destination. However the Multicast transformation is a synchronous transformation, thus the records will go to both outputs simultaneously and this could lead to blocking or deadlock issues. A simpler approach may be to just add an Execute SQL Task after the Data Flow Task that updates the source table based on the transferred records from the destination table. For the update, you'll want to do a set-based update, such as the SQL statement below. To only perform this update based on rows transferred within that package execution, a Multicast could used in the Data Flow Task to output only the unique IDs to a staging table, and then update the source based on the matching IDs in the staging table in the subsequent Execute SQL Task. Just make sure to add a step to truncate the staging table in the beginning of the package to clear data from the prior execution.

UPDATE SRC
SET SRC.SSIS_TRANSFERRED = 1
FROM dbo.SourceTable SRC
INNER JOIN dbo.DestinationTable DEST 
ON SRC.ID = DEST.ID
Related Question