Here's a more practical example of using constraints based on variables.
I have a package that requires an existing database file to be attached to the local SQL Server. The package is synced out to multiple machines from a source control system and run frequently. I don't want to manually setup the database on each machine, nor do I want to store the large database file in our source control system… so I decided to make the setup steps part of the package.
My first attempt looked something like this:
1 - Initial attempt
I created a package with a Script task, two File System tasks, and an Execute SQL task. The Script task branches the execution path based on whether or not the DB file exists on the local file system. This sort of worked – the file was copied, but the "Attach Database" task failed to run.
2 - Execute SQL Task does not execute
I soon realized that if you branch your logic like this, you need to set the constraints on the task where your execution merges again (in this case, "Attach Database") to OR's instead of AND's.
3 - Change constraint to logical OR
Now when I run, the file is copied, and the database task is executed. On subsequent runs, the file copying tasks are skipped and "Attach Database" is called immediately.
4 - It works... kind of
Although I could live with an error appearing the first time the script is run, it threw off some of our scripts, and people who weren't familiar with the package would always freak out the first time they saw it.
Error: 0x4 at Check if DB Files Exist: The Script returned a failure result.
Task failed: Check if DB Files Exist
While my initial solution (have my Script task print out the message "You can safely ignore the following error" when the file didn't exist) calmed the masses, it was still a problem for the execution scripts (not to mention just bad form!). This is how I discovered how to create constraints with conditional expressions.
I added a new Boolean variable to the package, and modified my script to set its value instead of setting success or failure.
5 - Package variables
My script:
Public Sub Main()
Dim strFile As String = Dts.Variables("dbFile").Value.ToString()
Dts.Variables("varFileExists").Value = File.Exists(strFile)
Dts.TaskResult = Dts.Results.Success
End Sub
(Be sure to add the variables you use to the script task to the "ReadOnlyVariables" and "ReadWriteVariables" property of the script task. Also note that you'll need to import System.IO for the File.Exists() method)
I edited the constraints connected to the script task, set "Evaluation operation" to "Expression", and added an expression based on the varFileExists variable.
6 - Setting expressions
This gives us a much cleaner solution! Now we don't see any errors the first time the package is run, and our files are copied only once. We're good to go!
NOTE: After I finished writing this up, I stopped by the SSIS forums and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=859001&SiteID=1 where someone had encountered a similar problem, and http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/ about it. I thought I'd link it here.
