Hello, I'm looking for an alternative to a precedence constraint, purely for the reason that I read in a decipherinfosys.wordpress.com article that precedence constraints should be avoided whenever possible. My package takes a list of database names into a Foreach Loop and, for each database name in the list, attempts to find a *.bak file with the same name in the Backup folder. The next task--Execute SQL--executes RESTORE DATABASE FILELISTONLY to get the logical file names. This task fails if and when the backup file with the expected name can't be found. Instead of failing, I need the execution to increment the loop counter and resume the loop execution at the top. I've used a precedence constraint between the two tasks: continue only if the "@filename" variable is not blank. This works perfectly, since a missing backup file causes neither a task failure nor an exit from the Foreach loop. What would be an alternative to using a precedence constraint in the Foreach loop, especially considering that I don't want to exit the loop altogether? Thanks, Eric

what is the reason to avoid precedence constraint? could you get us the link to that article? I didn't experienced any Bad behavior with precedence constraint.http://www.rad.pasfu.com

what is the reason to avoid precedence constraint? could you get us the link to that article? I didn't experienced any Bad behavior with precedence constraint.http://www.rad.pasfu.com

what is the reason to avoid precedence constraint? could you get us the link to that article? I didn't experienced any Bad behavior with precedence constraint.http://www.rad.pasfu.com

I'm looking for an alternative to a precedence constraint, purely for the reason that I read in a decipherinfosys.wordpress.com article that precedence constraints should be avoided whenever possible. My package takes a list of database names into a Foreach Loop and, for each database name in the list, attempts to find a *.bak file with the same name in the Backup folder. The next task--Execute SQL--executes RESTORE DATABASE FILELISTONLY to get the logical file names. This task fails if and when the backup file with the expected name can't be found. Instead of failing, I need the execution to increment the loop counter and resume the loop execution at the top. I've used a precedence constraint between the two tasks: continue only if the "@filename" variable is not blank. This works perfectly, since a missing backup file causes neither a task failure nor an exit from the Foreach loop. What would be an alternative to using a precedence constraint in the Foreach loop, especially considering that I don't want to exit the loop altogether? Hi Eric, I would implement some kind of error handling inside the proc to factor in for files that don't exist and have them return an expected condition. From there, you can set the remaining functions inside the loop to execute when a said condition exists using its expression property. For example, if you were to group the SQL task that is failing in a sequence container, then group the rest of the execution in a sequence container...all of which is contained within the loop. If you set an expression in the second sequence container's disable property to User::Filename == "" that will prevent that sequence container from being executed when you return a specified condition from the execute SQL task. In doing so, you continue through the loop and hit your next iteration without using a precidence constraint (except a success constraint between the Sequence Containers). I think that is what you're looking to accomplish; to avoid adding unnecessary precidence constraints. Jon EDIT: Here is a write-up that explains using expression properties in SSIS. http://msdn.microsoft.com/en-us/library/ms141214.aspx

Hi Reza, If I recall correctly, the reason precedence constraints should be avoided [I read] is not because of any bad behaviour, but instead because of some other reason that I can't remember. I believe it was a matter of logging, but I don't have enough experience with precedence constraints to know what kind of problem, or lack of tracking, that it would lead to. Thanks, Eric

Hi Jon, Thanks for your advice, it's much appreciated. Thankfully, I didn't have to read that entire page you recommended--your text explained it quite well. :) I was able to set this up easily, and it worked sufficiently, just as the precedence constraint that I set up earlier was able to do. At this point, I haven't decided which I'm going to use in production. Until I can find the reason precedence constraints were discouraged--was it because a view of the control flow screen can't show the precedence constraint, requiring the user to double-click it for an idea of how execution flow will (or won't) progress?--I guess either one will work fine. Thanks again, Eric

Hi Eric, Thank you, I'm glad you found my contribution helpful. I've found in my experience evaluation of several precidence constraints to be a hassle from a debugging standpoint. It is complicates the ability to look at a control flow and really get a feel for how a package runs at a glance. Granted SSIS functionality allows for use of multiple constraints but why complicate if you don't have to? This thread demonstrates how precidence constraints can get out of control and make things harder to debug. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/5710fc8f-6e60-4f4f-b0e9-f3e2278eac14/#ba8b02be-960c-4d5e-8b06-37c4481f63a2 It really depends on how much logic your package has, what kind of decisions you are making and how often they need to be made. There really isn't a right or wrong but avoiding excessive constraints makes life a lot easier when you need to debug. You'll get a feel for it the more time you spend with SSIS and/or with peers that write packages.

Hi Eric, Thank you, I'm glad you found my contribution helpful. I've found in my experience evaluation of several precidence constraints to be a hassle from a debugging standpoint. It complicates the ability to look at a control flow and really get a feel for how a package runs at a glance. Granted SSIS functionality allows for use of multiple constraints but why complicate if you don't have to? This thread demonstrates how precidence constraints can get out of control and make things harder to debug. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/5710fc8f-6e60-4f4f-b0e9-f3e2278eac14/#ba8b02be-960c-4d5e-8b06-37c4481f63a2 It really depends on how much logic your package has, what kind of decisions you are making and how often they need to be made. There really isn't a right or wrong but avoiding excessive constraints makes life a lot easier when you need to debug. You'll get a feel for it the more time you spend with SSIS and/or with peers that write packages.