31 Days of SSIS – One Variable to Rule Them All (5/31)

Posted by & filed under , , , , , .

31 FingersHopefully 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.

image

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.

SNAGHTML5bf7a1c

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.

SNAGHTML5bf5934

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.

SNAGHTML5be701e

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:

image

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:

SNAGHTML62d05ca

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

SSIS Package

OneVariable

  • http://ericwisdahl.wordpress.com Eric Wisdahl

    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/

    • http://feeds2.feedburner.com/StrateSql jstrate

      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.

  • http://bradsruminations.blogspot.com/ Brad Schulz

    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

    • http://feeds2.feedburner.com/StrateSql jstrate

      Thanks for the catch. Seems I put the value instead of the expression. Durnit. Fixed it now, though.

  • Pingback: Tweets that mention 31 Days of SSIS – One Variable to Rule Them All (5/31) | Strate SQL -- Topsy.com

  • RobinC

    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 :)

    • http://bradsruminations.blogspot.com Brad Schulz

      Hi Robin…

      Actually, it's "ellipsis"… with two L's.

      8^)

      –Brad

      • http://feeds2.feedburner.com/StrateSql jstrate

        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.

        • RobinC

          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

    • http://feeds2.feedburner.com/StrateSql jstrate

      I am pretty sure I have some lookup posts planned in the next week.

  • http://thesmilingdba.blogspot.com/ Thomas LeBlanc

    Great series

    Thomas

    • http://feeds2.feedburner.com/StrateSql jstrate

      Thanks

  • Pingback: 31 Days of SSIS – The Introduction | Strate SQL

  • Pingback: SQL Server Central

  • Pingback: 31 Days of SSIS – Override Variables (9/31) | Strate SQL

  • Pingback: Jim Croce and the Foreach (File) Loop Container | Mark V SQL

  • Salam

    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?

  • Pingback: Re-blog – December 30 – January 5 | Strate SQL

  • Pingback: Daily Series: 31 days of SSIS

  • Pingback: 31 Days of SSIS - Jim Croce and the Foreach (File) Loop Container (28/31) | Strate SQL

  • Pingback: 31 Days of SSIS - Recursive SSIS Package (6/31) | Strate SQL

  • Peace

    Please continue Sir, very clear and useful tips.