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


SSIS precedence constraint, check my logic please


SSIS precedence constraint, check my logic please

Author
Message
jasona.work
jasona.work
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33344 Visits: 15169
All,

I'm an SSIS newbie, putting together an SSIS package to push data from a source server to several destination servers. The destination table that is going to be populated is going to be truncated (the data is for reporting purposes,) with no need to save anything. I've done a QA test (which is a 1-to-1 push,) I know I can use a multi-cast in my dataflow, but it's one thing in the control flow I want to confirm.

At the moment, until I check with the customer, I want to work on the presumption that if any of the truncate tasks fail, the package should cease, rather than trying to load the data. If I'm reading the information in the Precedence Constraint editor screen correctly, by having ALL the incoming precedence constraints set to be Logical ANDs, it should work as desired.
(I've got 5 Execute SQL Tasks all feeding into the Data Flow Task)

Thanks
Jason
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110607 Visits: 18620
Jason

Yes, with AND constraints, the task will only execute if all precedents were successful. But do test it for yourself by engineering one of them to fail!

John
RonKyle
RonKyle
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21808 Visits: 4169
I've got 5 Execute SQL Tasks all feeding into the Data Flow Task


Consider putting your truncate statements in the same SQL Execute task.




Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (176K reputation)SSC Guru (176K reputation)SSC Guru (176K reputation)SSC Guru (176K reputation)SSC Guru (176K reputation)SSC Guru (176K reputation)SSC Guru (176K reputation)SSC Guru (176K reputation)

Group: General Forum Members
Points: 176438 Visits: 23545
jasona.work - Wednesday, June 14, 2017 9:14 AM
All,

I'm an SSIS newbie, putting together an SSIS package to push data from a source server to several destination servers. The destination table that is going to be populated is going to be truncated (the data is for reporting purposes,) with no need to save anything. I've done a QA test (which is a 1-to-1 push,) I know I can use a multi-cast in my dataflow, but it's one thing in the control flow I want to confirm.

At the moment, until I check with the customer, I want to work on the presumption that if any of the truncate tasks fail, the package should cease, rather than trying to load the data. If I'm reading the information in the Precedence Constraint editor screen correctly, by having ALL the incoming precedence constraints set to be Logical ANDs, it should work as desired.
(I've got 5 Execute SQL Tasks all feeding into the Data Flow Task)

Thanks
Jason

The way I would do this is to put the truncates inside a single Sequence container and then have a single precedence constraint running from the container to the data flow.
Functionality should be the same, but it's tidier and groups things logically.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
jasona.work
jasona.work
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33344 Visits: 15169
Phil Parkin - Wednesday, June 14, 2017 9:49 AM

The way I would do this is to put the truncates inside a single Sequence container and then have a single precedence constraint running from the container to the data flow.
Functionality should be the same, but it's tidier and groups things logically.

I was thinking of doing that, but I wasn't sure if it would work that way. I'd test, but I don't have a test setup at work and I'm trying to get this off my plate.

Which, having just spoken with the customer, they want it to keep going regardless of if one of the truncates fails, so I've flipped the constraints over to OR constraints.

Thanks for the help everyone!

RonKyle
RonKyle
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21808 Visits: 4169
they want it to keep going regardless of if one of the truncates fails, so I've flipped the constraints over to OR constraints.


Not sure I'd use OR in this case. You want them all completing. I've used OR infrequency, usually because there are multiple paths and only one can be used based on some earlier logic. You may simply want to change the precedence constraints to Complete, so that succeed or fail all will have to complete.

I also still don't think this precludes putting them in the same step as I and Phil recommend. Set the precedence to Complete as well.




bmg002
bmg002
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16677 Visits: 2476
jasona.work - Wednesday, June 14, 2017 10:33 AM
Phil Parkin - Wednesday, June 14, 2017 9:49 AM

The way I would do this is to put the truncates inside a single Sequence container and then have a single precedence constraint running from the container to the data flow.
Functionality should be the same, but it's tidier and groups things logically.

I was thinking of doing that, but I wasn't sure if it would work that way. I'd test, but I don't have a test setup at work and I'm trying to get this off my plate.

Which, having just spoken with the customer, they want it to keep going regardless of if one of the truncates fails, so I've flipped the constraints over to OR constraints.

Thanks for the help everyone!

I agree with RonKyle. If you use OR, as soon as 1 of the steps completes, it will start the data flow task which will likley be blocked by the truncates but may not be.
I would use the sequence container (as Phil pointed out) and set the output of that to go to the Data Flow task on Completion instead of on success or failure (which RonKyle also suggested).
Having all of the truncates in 1 step is iffy too. Having the 5 steps truncating means that 1 or more can fail and the others will succeed. With them all in 1 step I believe (not 100% sure, but fairly confident) that if any of them fail, it will roll back the changes. So if the 5th truncate failed in the 1 step scenario, none of the tables would be truncated.

I would confirm with the end user about duplicate data though. If one of the truncates fails and you go to the data flow task, presuming there are no constraint violations, any table that failed to truncate is at risk for having duplicate data as the data flow task doesn't do any duplicate data checking on its own unless you implement it.

RonKyle
RonKyle
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21808 Visits: 4169
Also, the TRUNCATE is such a basic command, it's hard to see it failing. I can't recall that I've had a TRUNCATE fail in production, and only in development when I've forgotten to remove it as the table doesn't exist anymore.



bmg002
bmg002
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16677 Visits: 2476
RonKyle - Wednesday, June 14, 2017 1:29 PM
Also, the TRUNCATE is such a basic command, it's hard to see it failing. I can't recall that I've had a TRUNCATE fail in production, and only in development when I've forgotten to remove it as the table doesn't exist anymore.

That is a good point. I've not even seen blocking caused by a TRUNCATE. I've seen TRUNCATE get blocked before though.

jasona.work
jasona.work
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33344 Visits: 15169
Thanks again everyone. I'm working on, after hours at home, getting a home test bed stood up so I can muck around with this without actually doing it against "real" servers.

I was somewhat concerned about the possibility of the data load trying to start while one or more of the target servers was still truncating the target table, so the Sequence container (presuming it works as I'm thinking from your descriptions it should) will probably be the way to go.

As for constraints, etc on the target table, there are none, it's a stand-alone table, there aren't even any indexes (the data going in isn't very much.) It's even possible duplicate data wouldn't be a problem for the customer for their purposes.
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