SQL Server 2012 Integration Services – Package Variables

In the recent articles published on this forum, we have been presenting new features of SQL Server 2012 Integration Services. Our presentations have been based so far on the Release Candidate 0 (RC0) of the product, offered as a free download since mid-November 2011. Now we can take advantage of the final version incorporated into the Released to Manufacturing (RTM) bits (currently in the evaluation format, with general availability expected on April 1, 2012). While RC0 was considered to be feature-complete, the RTM build should exclude any newly discovered bugs, making your initial testing somewhat less challenging.

These product changes are also reflected by new versions of sample AdvantageWorks and AdvantageWorksDW databases, published on the CodePlex site. Download them to an arbitrarily location on your SQL Server hosting Integration Services instance (we will use for this purpose C:Data folder), launch SQL Server Management Studio, connect to the target Database Engine, and select New->Query with Current Connection entry from the File menu. In the resulting query window, type the following in order to attach the database files and recreate their logs (as the result, both database entries should appear under Databases folder in the Object Explorer window):

CREATE DATABASE AdventureWorks
            ON (FILENAME = 'c:DataAdventureWorks2012_Data.mdf') 
            FOR ATTACH_REBUILD_LOG
CREATE DATABASE AdventureWorksDW
            ON (FILENAME = 'c:DataAdventureWorksDW2012_Data.mdf') 
            FOR ATTACH_REBUILD_LOG

In addition, we will leverage our sample SSIS project (containing a single .dtsx package), whose creation we described in our previous article. As you might recall, its sole purpose was to copy content of [dbo].[FactCurrencyRate] table in the AdventureWorksDW database to a text file, where the path and name were hard-coded in the corresponding Flat File Connection Manager. Our intention is to increase flexibility of this configuration by taking advantage of SSISvariables.

Despite a number of significant functional changes, including several mechanisms that considerably simplify modifications of package and project settings both inside and outside of the development environment (which we will discuss in more detail in our upcoming articles), variables still remain a viable choice in a variety of scenarios (including improving package portability and simplifying their maintenance). Their primary purpose is to provide runtime storage for values utilized by SSIS components, such as containers, tasks, event handlers, and precedence constraints, as well as the packages themselves. They also facilitate the use of properties that might change from one package execution to another as well as serve as loop counters, parameters of stored procedures, or components of more complex expressions.

In general, there are two types of variables – System and User (which occupy System and User namespaces, respectively). System variables are predefined and, while their list cannot be customized, it has been expanded in SQL Server 2012 by including IgnoreConfigurationsOnLoad (of Boolean data type), ProductVersion and LastModifiedProductVersion (of String data type), as well as ServerExecutionID (of Int64 data type). The only modifiable property of System variables is their ability to raise an event when their value changes (which can be either enabled or disabled). This is one of the primary distinctions that separate them from User variables, which can be created on an as needed basis and for which the majority of properties can be arbitrarily changed. User variables are limited to the scope of an SSIS container, which hosts their definition (the scope includes automatically all of its subcontainers). This means that while all of System and User variables on the package level are accessible from any of its components, the ones that have been created within a container, task, event handler, or precedence constraint, are not visible outside of it. In cases where variable names conflict, the ones with more localized scope take precedence. You can minimize the possibility of such occurrences by introducing custom namespaces (other than the System and User), but only for User variables. Variables can be assigned either a literal value or an expression (SQL Server 2012 introduces the ability to view such expressions directly in the Variables window within the SQL Server Data Tools), but in both cases, they must possess a specific data type.

Let’s see how this concept can be applied to our sample package to eliminate some of its hard-coded values. Our new, slightly more complicated example will employ variables to designate name and location of the target file (where data exported from the [dbo].[FactCurrencyRate] will be copied to). In addition, instead of relying on the Table or view data access mode in the OLE DB Source Editor, we will employ SQL command from variable approach. To accomplish this, we will create two additional variables – one of them representing the filter, which will limit output to rows where the CurrencyKey value is equal to 3 (corresponding to Argentine Peso) and the other, in the form of expression, containing the entire SQL query (which includes the filter variable). Effectively, our modified package will use variables to extract rows of [dbo].[FactCurrencyRate] containing entries for Argentine Peso and output them to a file with arbitrarily assigned location and name.

With the project opened in the SQL Server Data Tools and the package displayed in its Designer area, switch to the Data Flow tab and select Variables from the submenu of the Other Windows item in the Viewmenu. In the resulting window, use the left-most icon in its toolbar to create four variables with the following settings:

  • sFileLocation of String data type and its value set to c:DataLab1
  • sFileName of String data type and its value set to APFactCurrencyRate.txt
  • sFilter of String data type and its value set to 3
  • sQuery of String data type and its Expression set to “SELECT * FROM [AdventureWorksDW].[dbo].[FactCurrencyRate] WHERE [CurrencyKey] = ” + @[User:sFilter]. The expresion’s syntax (which you can explore in more detail by using Expression Builder interface) indicates that sQuery references sFilter variable. Note that expression-based variables can be easily identified based on their graphical representation in the Variables window. In addition, for obvious reasons, their EvaluateAsExpression property is set to True and their Value is read-only.

With the variables in place, switch to the Data Flow tab of the Designer window and use the context sensitive menu of OLE DB Source component to display its Editor window. In the Connection Manager section, select SQL command from variable entry as the Data access mode and User::sQuery as the Variable name. Verify that the correct content appears in the Variable value area, use the Preview command button to examine the query outcome, and close the Editor window. Next, switch to the Connection Managers window and display the Properties window of the Flat File Connection Manager. Click the ellipsis (…) button next to the Expressions entry. In the Property column of the Property Expressions Editor, select ConnectionSting, and use Expression Builder to set its value to @[User::sFileLocation] + @[User::sFileName]. Click on Evaluate Expression command button to ensure that you get the expected result and close the window, returning to the Designer interface. At this point, you should be able to execute your package with the new values assigned via SSIS variables.

While values of variables were set in this case directly within a package, they might also be assigned through other means at the time of package invocation (allowing you to alter package behavior and outcome, without direct modifications of its content). This, in turn, significantly reduces potential maintenance and version control issues, simplifying package development, deployment, and management. There are also other methods, introduced in SQL Server 2012 Integration Services that allow you to accomplish the same objectives. We will explore them in upcoming articles published on this forum.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles