This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012. So far, we’ve covered:
- Tables and Schemas in the data warehouse
- Dimensions and Slowly Changing Dimensions
- Fact tables and measures
- Intro to columnstore indexes
- Columnstore indexes and partitioning
- Introduction to SQL Server Data Tools (SSDT)
- Connection Managers
- Control Flow Tasks and Containers
- Data Flow Source Adapters
- Data Flow Destination Adapters
- Data Flow Transformations
Today we continue our series by discussing SSIS variables and parameters.
Intro to Variables
There are two kinds of variables in SSIS: user-defined and system. User-defined variables are created by the developer (aka you) to store dynamic information or results during execution. The value of user-defined variables can be set and changed at any time during the lifetime of the variable. They can be useful for making your packages more dynamic and reusable. User-defined variables can be used to hold parameters to be passed into an Execute SQL Task or they can hold the results of that task. The entire TSQL of that task can be held in a variable, for that matter. Variables are also used in looping containers, such as the Foreach Loop Container, to hold rowsets or file names that need to be processed.
System variables are a set of pre-defined variables that represent specific properties within SSIS. Unlike user-defined variables, the value of system variables are set by SSIS and cannot be changed. Some examples of system variables are ContainerStartTime and StartTime, which hold the time the container or package started to run, respectively. These can be useful for logging/auditing within your package. Variables like ErrorCode and ErrorDescription are great for error handling (which you’ll always incorporate into your packages, right?).
Scope
Every variable will have a defined scope that will determine when and where a variable will be visible. This scope and visibility has a lot to do with the hierarchy within SSIS. A variable created within the package scope is considered global, because the package is at the root of the hierarchy. You’ll be able to access that variable from anywhere in the package. However, let’s say you create a Foreach Loop container to iterate through files in a directory. You create a variable in that container to hold the current filename being processed. That variable will be visible to the container and any task you create in that container. However, it won’t be visible to a task outside of that container. If you then add a task to that container, and create a variable in that task, that variable will only be visible to that specific task.
Variables vs. Parameters
The terms variable and parameter tend to be thrown about interchangeably. They are, in fact, separate concepts with one big distinction: variables are dynamic and can be changed during execution whereas parameters are passed in at execution time and are then static. Parameters are intended to expose properties to the user/administrator at run time. ConnectionStrings, instance and/or database names are commonly parameterized to aid in portability. This way you can run a package against different environments without having to edit the package every time.
Additional Resources
For more information on variables and parameters, check out the following resources.
