SQL Server Integration Services’ primary purpose is to serve as a database development platform. As such, it is subject to standard best practices of software programming, which among other recommendations, advocate reusability. Realizing this objective in SSIS frequently involves the use of variables. Their characteristics and most common usage scenarios will be the primary subject of our presentation.
While the Integration Services component of SQL Server 2008 can be utilized to implement ad hoc data migration tasks (by employing such tools as Data Import and Export Wizard), its primary purpose is to serve as a development platform. As such, it is subject to standard best practices of software programming, which among other recommendations, advocate reusability. In the case of SSIS, realizing this objective frequently involves the use of variables (we will present another method that serve the same purpose in our next article). Their characteristics and most common usage scenarios will be the primary subject of our presentation.
Even without more extensive coding experience, it is fairly easy to recognize the significance of SQL Server Integration Services’ variables. Their most obvious role is to provide temporary storage for values used by SSIS constructs, such as containers (e.g. the Foreach Loop container gives you an option to map the current collection value to an arbitrary variable), tasks (for example, the Execute SQL Task allows you to preserve its results in variables, as well as to use them as parameters in queries and stored procedures), Data Flow sources ( the OLE DB Source accepts variables designating table names or T-SQL commands), precedence constraints (capable of using expressions containing variables during evaluation operation), or event handlers (which define custom actions to be carried out in response to completion of an arbitrarily selected event and rely heavily on several predefined system variables).
Storage provided by variables is commonly needed either in order to facilitate data exchange between components that are not able to communicate directly or simply due to convenience (and a decreased potential for accidental typographical errors) in situations where the same value needs to appear multiple times within the same package. Variables are also frequently used when building expressions (incorporated into such tasks as Derived Column Transformation or Data Conversion, which we have discussed previously). Even more importantly, (which might not be immediately apparent), variables enhance the dynamic nature of packages by providing a superior alternative to hard-coding specific configuration settings that could change during a package lifetime. This requirement is common when staging package deployments, which involves transitioning them between development, quality assurance, user acceptance testing, and production environments. It also is applicable in scenarios where multiple copies of a single package run against different data stores.
SSIS Variable Criteria
SSIS variables can be categorized based on a number of different criteria, which in turn dictate their basic characteristics. These criteria include:
Type
Type defines content that can be stored within a variable. In general, a variable can host either a literal value or an expression. Note that such expression cannot reference data flow columns, but instead has to consist exclusively of a combination of literal values, operators, and functions. In either case, the actual content needs to have a data type that matches the ValueType property assigned to the variable, which you can determine by examining its Properties window. Interestingly, the number of choices available in the Designer interface of Business Intelligence Development Studio is relatively limited and includes Boolean, Byte, Char, DateTime, DBNull, Double, Int16, Int32, Int64, Object, SByte, Single, Sring, UInt32, and UInt64, which represent a larger set of Integration Services data types (for their full listing, refer to the MSDN article Integration Services Data Types). Note that since this property cannot change dynamically during package execution; any attempt to set variables to values of an incompatible data type will trigger an error (as a matter of fact, the only variable operation that is allowed at runtime is a change to its value).
Namespace
Namespace provides a logical context in which uniqueness of a variable is determined (in contrast to package-hierarchy based context utilized by scope, which we discuss next). It is represented by the Namespace property of a variable, and in combination with the variable name, forms its QualifiedName read-only property. Since it is not possible to have duplicate variable names within the same namespace, creating custom namespaces mitigates name conflicts.
Every SSIS implementation contains System and User namespaces by default. The former contains a number of predefined variables (whose properties, for the most part, cannot be modified), while any newly created, custom ones become part of the latter. However, you also have the option to assign them to another, arbitrarily chosen namespace.
It is worth noting that the collection of System variables varies depending on the scope. This is most apparent in the case of event handlers (mentioned briefly earlier), which use them to deliver their specialized feature set. For example, SourceName, SourceID, SourceGUID, and SourceDescription are used by the OnError event handler to identify the component where the error was generated (and which triggered the event). Details of that error, in turn, are captured using ErrorCode and ErrorDescription variables. (We will be discussing this functionality in more detail in future articles).
Scope
Scope, represented by the Scope property of a variable, designates the name of an SSIS object where the variable has been created. Its value is particularly significant since it restricts visibility of the variable to that object and its children (for example, defining a variable within a task does not make it available to other “sibling” tasks within the same package or to the package in which these tasks reside). This improves efficiency and overall simplicity since the number of variables applicable at a given object level is decreased.
As you might expect, based on the information given above, all variables within a given scope and namespace need to have unique names (this is equivalent to the requirement for the unique QualifiedName property of a variable within a given scope). This rule is enforced by the SSIS engine, so any attempt to violate it will fail. At the same rate, however, it is possible to have duplicate variable names, as long as their scopes (and namespaces) do not match (although, in general, you should avoid defining multiple identically named variables). In cases where variable names conflict (due to the scope of one being a sub-scope of the other), the variable with more localized scope takes precedence. You can minimize the possibility of such occurrences by introducing custom namespaces, although, as we mentioned above, this applies only to user variables.
Viewing and Modifying Variables of a Package
The most straightforward approach to viewing and modifying variables of a package or one of its components involves the use of the Variables window within the Business Intelligence Management Console interface (of course, it is also possible to manipulate them programmatically). You can trigger its display by selecting the Variables entry in the context sensitive menu of the Designer interface or any variable-aware component. (Your choice in this case should take into consideration the scope within which you intend to operate). Alternatively, you can also use the Variables entry in the SSIS top level menu. Initially, the resulting window does not contain any entries, since its default is not to display System variables, but you can quickly alter this behavior by clicking on the third (Show System Variables) button of its toolbar. The fourth (Show All Variables) button from the left lets you apply the equivalent action to User variables. To display the most commonly referenced variable properties, click on the rightmost button (Choose Variable Columns) and select all checkboxes in the corresponding dialog box. (For a comprehensive list, refer to the Properties window). The listing appearing in the Variables window makes it possible to quickly change the namespace of non-System variables (by typing a new value in their Namespace column), set their content (via the Value column), data type (using the Data Type column), or name (with the Name column). The two leftmost buttons in the toolbar allow you to create and delete custom variables respectively.
While reviewing the Properties window, note that you have the option of configuring a variable as read-only (by setting its ReadOnly property to TRUE) at design time. Another interesting option involves storing an expression in the variable and returning its current value whenever variable is referenced. This behavior is controlled by EvaluateAsExpression (set to either TRUE or FALSE) and Expression (containing the actual expression to be evaluated) properties of a given variable.
Additional Resources
SQL Server 2008 R2 Index
MSDN Integration Services Variables
Microsoft Integration Services
MSDN System Variables
