SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Task Question


SSIS Task Question

Author
Message
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15144 Visits: 9006
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15279 Visits: 14396
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
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15144 Visits: 9006
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15279 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search