opc.three (3/28/2013)
With only a single Precedence Constraint in place I am not aware of a configuration that would allow SSIS to execute a subsequent task without the previous task completing, whether that be Success, Failure or either one (Completion).
My thought is that the EXECUTE command doesn't get thrown at the database and then the package moves on after verifying the EXECUTE command ran successfully. It actually waits for a return code from the stored procedure itself before moving onto the next step. I just want to verify that I'm not misremembering this.
a couple other guesses:
1. If your Execute SQL Task and the Task that issues the TRUNCATE TABLE statements are not bound by the same database transaction then data could theoretically enter the source table after the stored procedure runs, but before the TRUNCATE TABLE statements are issued, meaning data could be forever lost.
Ummm... You lost me on this one. Data isn't entering the source table. It's going from the source to the destination, and the destination is indeed supposed to be loaded before the TRUNCATE TABLE statement occurs (TRUNCATE TABLE is run on the source).
My issue is the source seems to have been trucated and reloaded without the initial step of the data making it to the destination (which runs without errors).
2. Something (code, schema, data) changed and it rendered with the procedure that does the archiving ineffective. Have you verified procedure is still copying data as expected when not run through the SSIS Package?
I have verified that the code inside the BEGIN Transaction Try / Catch process works without issue (ran it in a SSMS query window). The code hasn't changed in two years, so either something is forcing to rollback at the point of execution without properly reporting the errors that it's coded to report (Catch clause) or something is coming in later and removing those records from the archive table.
I can't find any process that goes in later and removes records from the archive table and, as far as I know, none of our processes have changed in the past 2 years. Neither have our packages or the code. So I'm doing quite a bit of head desk this week, if you know what I mean.