I am using an SSIS dataflow to copy data from a source table "s" to a target table "t", which is empty. For the very next step after the dataflow, I have a "Exec SQL Script" that runs a delete statement that deletes from a table "z" where the recently copied data exists in table "t". What I have been noticing is that the delete statment appears to be firing prior to the data fully committing in the previous dataflow step. Sometimes not all the data is in table "t" and the result of the delete is incorrect. Is there a way to guarentee the dataflow has completed and committed prior to executing the next step? Or is this bug?
Asked
Active
Viewed 246 times
0
-
"appears to be firing". It's possible that what appears to be happening is not happening. Make certain of it. I've never had this issue myself. – Nick.Mc Oct 09 '13 at 00:39
-
I had the same doubt. So I enhanced the delete script by enbedding the "delete" statement in a loop. The instructions were to keep trying to delete until data was found in table "t". I piped the loop count result to a table. What I found was the loop count total was anywhere from 0 to 1758, meaning at times there was a delay in seeing the data in table "t". Eventually it got data, but not always immediately. Same transaction run at differnent times yielded different results (i.e. loop count totals). Strange. – user2859231 Oct 09 '13 at 12:16