Hopefully by now you’ve read the introductory post for the 31 Days of SSIS series that I am writing. Today we are going to look at variables in SSIS packages.
This post assumes that you already know a bit about variables in SSIS packages. If there is a groundswell of requests, I can do a post on variables themselves. So ask if you want to see it.
Time to begin…
Variable Expressions
Back in the DTS days, in order to change the value of a variable we need to write VBScripts. These scripts were at best irritating to write and were wrought with opportunities for misspellings and unrunnable code.
Enter stage right with SSIS and its variables, which can be evaluated as expressions. This was a vast improvement over DTS and all of the VBScript that we had in the past was chucked.
But how exactly do we evaluate variables as expressions. First, what does it mean to do this? When a variable is evaluated as an expression, the value for the variable is calculated at run-time based on SSIS package properties, system properties, and other variables to determine a value for the variable.
From here though, let’s see how this works with our own variable. To start create a package and open up the Variables window. In this window create a new variable named SimpleVariable.
Once the variable is created bring up the Properties window for the variable. There will be a number of things to choose from in the properties. The item we are concerned with is EvaluateAsExpression. Follow the red arrow below if cannot find the property. Change this value to True.
When you change the EvaluateAsExpression property to True, an ellipse will be enabled (at the red arrow below) that will allow you to open the expression builder for the variable.
Click on the ellipse and the Expression Builder window will appear. This is pictured below. There are a couple areas to pay attention to when building expressions. First, the area in green below contains all of the system and package variables that are available. If you are unsure on the name of a variable just browse through this list to retrieve one. Next, in the yellow area, has a list of all of the operations that are possible in expressions. These can be a bit tricky to get used to at first and I’ll dedicate a later post to some of their nuances.
For our expression at the moment, type in “1 + 2”. This is written in the Expression box above. Selecting the Evaluate Expression button will return the result of 3.
This was a simple explanation of how to build an expression for a variable. Now onto something much more useful.
The One Variable
Yesterday’s post focused on the configuration of the SSIS environment form a folder perspective. The reason for that post was because I wanted to talk today about how I will often use one variable in a package as a fulcrum for setting many other variables and configuring other portions of the SSIS package.
In the Folder Structure post, I talked about the WorkFiles folder. Often I include a variable that has this value which I use to set the values for files and folders that the package must interact with. The rest of this post will demonstrate this practice.
In an SSIS package, create the following variables; WorkFolder, ArchiveFolder, FileName, LogFolder, and ResponseFile. Set the value for the WorkFolder variable to “c:temp”. At this point your Variables window should look like this:
Now change the EvaluateAsExpression property for ArchiveFolder, FileName, LogFolder, and ResponseFile like we did in the previous example. What we want to do now is create expressions for these variables based on the value in WorkFolder. Type in the following expressions for each of the variables:
- ArchiveFolder: @[User::WorkFolder] + “\Archive\”
- FileName: @[User::WorkFolder] + “\ImportFile.txt”
- LogFolder: @[User::WorkFolder] + “\Log\”
- ResponseFile: @[User::WorkFolder] + “\Response\”
Once these are all set, the values for each of the variables should change to the following:
As you can see, the WorkFolder variable now controls the values for each of the other variables. By just changing the value of WorkFolder all of the locations for all of the files can be changed.
Variable Wrap-Up
Using a variable or two to control the configuration of your SSIS packages can is relatively simple to do. In the demo above, the one variable reduced the amount of configuration points needed for the package. Instead of setting all of the values individually, they are set by the one variable.
This can help reduce configuration changes when you are moving your SSIS packages from environment to environment. It can also reduce the amount of work required when troubleshooting a package since only a few variable will vary between environments.
Now that I’ve laid out some ground work, be prepared for a few packages in the next few days that I’ve used to solve business problems.
References
Getting Started with Variables in SSIS
How do you open the Variables Window?
LikeLike
I’m sure you’ve found it by now but with the control flow or data flow active click View >> Other windows >> Variables
LikeLike
Please continue Sir, very clear and useful tips.
LikeLike
Hi, wonderfull series.
When we change value of WorkFolder , it is not reflected right away in the other variables. Is it done later at run time?
LikeLike
Great series
Thomas
LikeLike
Thanks
LikeLike
These articles are great – looking forward to the rest.
If you're taking requests, can you do a post on streaming lookups.
(btw the … is called elipsis not an elipse 🙂
LikeLike
Hi Robin…
Actually, it's "ellipsis"… with two L's.
8^)
–Brad
LikeLike
Thanks for the catch. This is what happens when I finish writing these after my editor goes to sleep. I'll get that fixed when I make some image changes this weekend.
LikeLike
Haha, catcher caught out by Brad 🙂
Looking forward to your posts, I dabbled with lookups for a project at work but ran out of time to get it working properly before deadline.
Rob
LikeLike
I am pretty sure I have some lookup posts planned in the next week.
LikeLike
Oops… I think you forgot to use User::Workfolder in the definitions of LogFolder and ResponseFile.
I'm enjoying the series… looking forward to 16 more great entries.
–Brad
LikeLike
Thanks for the catch. Seems I put the value instead of the expression. Durnit. Fixed it now, though.
LikeLike
Jason,
Just as a heads up, the one variable to rule them all is something which I have (and sometimes still) adhered to in the past. However, there is a threading bug which can cause problems when you are referencing variables that use an expression, particularly with a master package calling several children in parallel with each referencing the single variable. The bug states that the issue has been fixed, but I haven't seen it applied in a service pack or CU to this point. So, just be mindful of that when applying this solution…
Hope all is well.
Eric
https://connect.microsoft.com/SQLServer/feedback/…
LikeLike
Oh, interesting. I haven't hit this before. This is most likely because I don't have many parent-child package executions. I am helping a client move towards that and will need to keep and eye on it.
LikeLike