Home Forums Programming SSDT SSIS precedence constraint, check my logic please RE: SSIS precedence constraint, check my logic please

  • June 14, 2017 at 1:14 pm

    #1946834

    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.