SSIS precedence constraint, check my logic please

  • 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

  • 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

  • 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.

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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!

  • 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.

  • 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.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • 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.

  • 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.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • 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.

  • I'd like to again say thank you to everyone who made suggestions here.
    I gave the package a test run this morning, with the Execute SQL tasks wrapped in a Sequence Container, using a completion constraint before hitting the data flow part and it worked like a charm.

    I'm going to do more work on it on my home lab, try to find new and unique ways to break it (what if the table being truncated isn't there?, what if the source server is offline or the data format changes?) and add in logic to deal with that.

    But currently, to get the customer going, I've got it!

    Not bad for someone who just wrote their first SSIS package.

  • jasona.work - Thursday, June 15, 2017 8:09 AM

    I'd like to again say thank you to everyone who made suggestions here.
    I gave the package a test run this morning, with the Execute SQL tasks wrapped in a Sequence Container, using a completion constraint before hitting the data flow part and it worked like a charm.

    I'm going to do more work on it on my home lab, try to find new and unique ways to break it (what if the table being truncated isn't there?, what if the source server is offline or the data format changes?) and add in logic to deal with that.

    But currently, to get the customer going, I've got it!

    Not bad for someone who just wrote their first SSIS package.

    Another thing to watch for too is what if the destination table isn't there.
    One thing that sucks with SSIS, but not sure it will ever change - if the metadata of the objects it uses change, the package will usually fail to run.  Sometimes it is a fixable thing by opening and recompiling the project, sometimes you need to rebuild the whole SSIS package.
    It is a fun tool when it works, but sometimes painful to debug it :(.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • bmg002 - Thursday, June 15, 2017 8:43 AM

    Another thing to watch for too is what if the destination table isn't there.
    One thing that sucks with SSIS, but not sure it will ever change - if the metadata of the objects it uses change, the package will usually fail to run.  Sometimes it is a fixable thing by opening and recompiling the project, sometimes you need to rebuild the whole SSIS package.
    It is a fun tool when it works, but sometimes painful to debug it :(.

    I believe this feature is called: 'SSIS Virtual SchemaBinding and Fault Intolerance' – a super awesome feature :hehe:

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply