Helpful information and examples on how to use SQL Server Integration Services.
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
Public Sub Main()
Dim strFile As String = Dts.Variables("dbFile").Value.ToString()
Dts.Variables("varFileExists").Value = File.Exists(strFile)
Dts.TaskResult = Dts.Results.Success
(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 noticed a post where someone had encountered a similar problem, and wrote a blog entry about it. I thought I'd link it here.
Nice post - It would be good to add which version of SSIS this is using. We need everything versioned, including our posts !
I believe this post was done before 2008 was available. :) Normally I mention the product version if what I'm talking about is version specific, or there are differences in behavior.
In this case, things are the same for both 2005 and 2008....
Good info on using multiple constraints. Thanks!
This is so cool! It really helped me big time.
Let me clarify why I think this is a BS of a product!!!!
1. In only supports VB!!! Ok fine I have to to live with it till version 2008.
2. In VB a logical comparison is a simple "=", so WTF with the "==" operator?
3. Why the F*)*)*_XK did they make it some complicated!!!!!!!!!!!
4. @[var_name] -- What WTF??!!!! I don't get this!!! FU MS.
I'm sorry you find the product complicated. We pay close attention to the constructive feedback we receieve through MS Connect, and continue to work on improving the user experience with each version of the product.
Yes, SSIS 2005 only supports VB.NET for scripting, as it was the only language available for the VSA scripting host that we used at the time.
SSIS 2008 switched over to the new VSTA environment, which provided C# support and a better designer experience.
Our expression language is not based on VB - it's actually closer to C. Most C based languages use = and == to distinguish between assignment and equality.
Please see the Expression Language reference for more information.
I'm not sure why the @[VariableName] construct was chosen, but it does make it easier for the expression parser to identify variable values and distinguish between variables and column names in data flow expressions (Derived Column, Conditional Split). I'm not sure we'd want to change the syntax of the language at this point, but if you have alternate suggestions, feel free to open an issue on https://connect.microsoft.com/.
Matt, Great post and it's no complecated in anyway!! I don't know what's this person's problem!! how did he land here if he doesn't know what you're talking about!!