SSIS data flow to update source table rows after copying to destination

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?

With T-SQL there's an output inserted clause which would be really helpful in this scenario.

I'm not aware of a way to do that with SSIS, so your alternate approach of using a second data flow after your first (you can connect the second data flow to the first so your dependency will be honored), querying the web database, and updating the internal one as appropriate will work as long as your workload can tolerate the additional latency involved in waiting to update the SSIS_TRANSFERRED column.

StackExchange Database Administrators Q#219159, answer score: 3