SSIS Precendence constraint question
-
I'm having a little bit of a brain disconnect this morning, so I want to talk this through and have someone point out the flaws (if any) in my logic.
My SSIS package has a variable (set by the config file) to send files via SFTP or not to send files via SFTP. When we're troubleshooting an issue, we don't want the SFTP step to happen. During normal runtime, we do. So there's a ForEach Loop that creates a series of files and then two precedence success links (expression and constraint) coming off the container. One constraint checks the variable @SFTPFiles == 1 (true), the other checks @SFTPFiles == 0 (false). Both are set to Logical OR.
Off the true constraint is the SFTP task. This leads to to the Zip & Archive files task (with an OR constraint) and a "Files Sent" email task (with an AND constraint) and the package ends successfully.
Off the false constraint is the Zip & Archive files task. If it fails, it leads to a failure email task and then the package ends successfully, otherwise the package ends successfully without hitting the failure email task.
When I ran the package yesterday, the zip task failed before the "Files Sent" email went out. I should have caught onto that problem. Now I want to force the zip to wait until after the "Files Sent" task. I've switched the precedence constraint from the SFTP task -> Zip & Archive to the "Files Sent" -> Zip & Archive. It's still a Logical OR.
This setup should be fine, correct? Even though I have two OR constraints going to the Zip & Archive, if the @SFTPFiles == 1, the Zip & Archive files should only kick off once the SFTP task and the "Files Sent" email kick off, right? Or do I need to add the variable expression to the other constraint (the bottom one) as well?
Picture added as attachment in case you can't see the one embedded in the post.
-
The flow to the SFTP File task doesn't need to be an OR, as only one task is flowing to it. If that flow doesn't evaluate to TRUE then the SFTP File task won't run.
The Zip Annual Files No Files No SFTP will resolve if either of the flows running to it evaluate to TRUE. So, if the flow between Create Annual Files and Zip Annual Files No Files No SFTP evaluates to TRUE, then that task will start, regardless of if the Email File Count to Team task if still running. If I understand correctly, this isn't the result you want, correct? (see below as an example)
Notice that the Select 1 and Wait task is still running, but Select 1 Again has already run, even though both Select 1 and Select 1 and Wait are both precedent constraints.I notice, however, that the Flow from Create Annual Files to Zip Annual Files No Files No SFTP has an expression on it as well. What is the expression in there, @SFTPFiles == 0? If so, that should work as you expect, as the task flow will evaluate to false if you want to send the files via SFTP, and it will wait for the Email File Count to Team task to complete.
I hope I've understood your goal correctly. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Friday, June 16, 2017 5:12 AMThe flow to the SFTP File task doesn't need to be an OR, as only one task is flowing to it. If that flow doesn't evaluate to TRUE then the SFTP File task won't run.
The Zip Annual Files No Files No SFTP will resolve if either of the flows running to it evaluate to TRUE. So, if the flow between Create Annual Files and Zip Annual Files No Files No SFTP evaluates to TRUE, then that task will start, regardless of if the flow if currently doing the Email File Count to Team task. If I understand correctly, this isn't the result you want, correct? (see below as an example)
Notice that the Wait task is still running, but Select 1 Again has already run.I notice, however, that the Flow from Create Annual Files to Zip Annual Files No Files No SFTP has an expression on it as well. What is the expression in there, @SFTPFiles == 0? If so, that should work as you expect, as the task flow will evaluate to false, and it will wait for the Email File Count to team task to complete.
I hope I've understood your goal correctly. 🙂
The flow from Create Annual Files to Zip Annual Files is set at @SFTPFiles == 0.
I think you have understood my goal correctly. Thank you for pointing out the issue with the SFTP task constraint. I forgot the OR is for incoming constraints, not outgoing constraints. Fixing that now. And again, I appreciate the extra perspective.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply