March 28, 2013 at 11:52 am
I have an SSIS package with two Execute T-SQL tasks linked by a precedent constraint. The first task kicks off a proc which takes data from six tables and loads it into the six archive tables. The second task has a series of TRUNCATE TABLE statements in the task itself (not a stored procedure) that then truncate the six tables.
I'm trying to trouble shoot an issue that popped up in the past few months where it seems the data is not getting loaded into the archive table.
Is it possible that the truncate statements could be firing off before the archive stored procedure finishes processing? In other words, does the Execute T-SQL task 1 finish before the stored procedure it contains is actually finished processing?
I might be way off base here, but this is the only scenario I have not 100% verified is not happening. It's my usual experience that the task shouldn't complete until the proc itself sends back a success or failure signal, but ... well, I've been wrong before.
Thoughts?
March 28, 2013 at 10:13 pm
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). Now, if there are multiple Precedence Constraints feeding the Task doing the TRUNCATE and the constraints are set to allow OR, instead of AND, then the TRUNCATE could run before the proc.
Without knowing a bit more about the process I can only offer 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.
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?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2013 at 7:54 am
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.
March 29, 2013 at 9:13 am
Brandie Tarvin (3/29/2013)
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.
Are you thinking there might be a bug in SSIS where the SQL is never executed and the Task allows the Package to proceed? You could try starting a Trace on the server before running the Package to see what actually makes it to the server, and when.
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).
This is where my knowledge of your environment, or lack thereof, gets in the way. I meant upstream from the source table. This was the scenario I was imagining:
1. Package starts executing and calls proc to copy data from source table to archive table
2. while the proc is executing some other process issues an INSERT INTO to add rows to the source table
3. the proc completes and now the TRUNCATE statements are run which promptly obliterate the newly inserted data
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply