Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Task Question Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 11:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
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?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1436641
Posted Thursday, March 28, 2013 10:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
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
Post #1436814
Posted Friday, March 29, 2013 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1436937
Posted Friday, March 29, 2013 9:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
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
Post #1436971
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse