alex.sqldba (12/9/2016)
Hiya, thanks! I like this approach of using the delete/output as an SSIS Source.However.
I am using the ADO.NET provider. And it's working fine, except in one particular occurrence: Should I stop the package whilst it's running, I see that rows are appended to the Archive Table, from the output portion -- but they are not deleted on the source.
Is there a way to make it so that they ALL going AND get deleted or rolled back as one batch?
I am concerned that on an occasion someone kills the job, it will duplicate those records the next time it succeeds.
Cheers!
Alex
It's rather surprising to hear that the OUTPUT is sending rows which have not actually been deleted.
A less elegant, but more bulletproof way of doing this would be to have the OUTPUT write to a temp table instead, and then use a SELECT from the temp table as your data source (you'd have to use EXECUTE WITH RESULT SETS to 'tell' SSIS what resultset structure to expect).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.