CODEWITHSUNDEEP
sql-serverssis
Dale Burkell
Dale Burkell

Reputation: 23

SSIS prevent delete after insert if insert fails

I have a requirement to use SSIS to pull data from somewhere, put it in a SQL database table, then delete the source data. I have seen this answer SSIS : delete rows after an update or insert and option #3 (add a multicast to split the 2 operations) looks good but I have a concern...

What happens if the insert to the destination fails? Will my records still be deleted? I want to make sure I don't lose any data by accident due to a failed insert - is there a better way?

I'm using SQL Server 2012 to execute the SSIS package.

Thanks

Upvotes: 2

Views: 277

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

If you use a multicast, then yes, the delete will happen even if the insert fails. If you want to make the delete dependent on the insert, you would need to do them in separate consecutive tasks with a precedence constraint between them.

Upvotes: 1

Related Questions