Overview: This is a high level review of the terminology for configurable items like parameters and variables in SQL Server Integration Services 2012. This discussion is applicable to the Project Deployment Model only.
Following is a high level flowchart of how values can be passed to parameters, variables and connection managers in SSIS 2012. The left side represents the SQL Server Data Tools environment in Visual Studio (i.e., during development before the project has been deployed); the right side represents the SSIS Catalog in the Management Studio environment (i.e., after deployment).
The remainder of this blog entry will discuss individual components of the above flowchart.
SSIS Project & Package Parameters
Project parameters are new with SSIS 2012. A project parameter can be shared among all of the packages in an SSIS project. You want to use a project parameter when, at run-time, the value is the same for all packages.
Package parameters are also new with SSIS 2012. A package parameter is exactly the same as a project parameter – except that the scope of a package parameter is the individual package it resides in. You want to use a package parameter when, at run-time, the value is different for each package.
Note that project parameters do *not* have an expressions property to define their value. They are intended to hold a literal value which does not change while the package executes.
See in the image at the top of the page that project parameters can pass a value to variables? Parameters can also pass values to all kinds of objects in SSIS – basically any property that allows an expression.
You can think of parameters are the replacement for package configurations which were used in earlier versions of SSIS.
More details on defining parameters: http://blogs.msdn.com/b/mattm/archive/2011/07/16/configuring-projects-and-packages-using-parameters.aspx
SSIS Variables
There’s actually not too much new here with *package* variables in SSIS 2012 (other than you can move them now which is great). What can get confusing is that sometimes the environment variables are just referred to as variables – so you need to be aware of the context in which variables are being discussed. (Environment variables are discussed in the next section below.)
Within a package, SSIS variables have an expression property. The expression property, and the ability to change values during the execution of a package if needed, are two fundamental differences variables have from parameters. A variable can be scoped to the package or an object within the package (there’s no such thing as a project variable though).
Variables often consume values from parameters within their expressions (an example of this is in the next screen shot). Variables can also communicate values to objects like Execute SQL tasks, For Each containers, Send Mail tasks, and so on.
More details on SSIS variables: http://technet.microsoft.com/en-us/library/ms141085.aspx
More details on the differences between parameters and variables: http://www.rafael-salas.com/2011/12/ssis-2012-parameters-and-variables-what.html
More details on SSIS variables: https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/
SSIS Environment Variables
SSIS environment variables are new with SSIS 2012. You actually interact with these in Management Studio after the project has been deployed to the SSIS Catalog. Don’t confuse these with Windows environment variables – although named the same, SSIS environment variables are different than Windows environment variables. Also don’t confuse these with “regular” variables used within SSIS packages (which are discussed in the previous section above).
An environment variable provides the flexibility to configure values for parameters and connection managers in Management Studio which are different from what was originally specified when the package was deployed. This is great functionality for the administrator of the SSIS and ETL processes.
An environment(s) and its variables are set up in Management Studio under Integration Services Catalogs. Once set up under the Environment Properties, these variables can be associated to projects and/or packages. Since their purpose is to override parameters or connection managers, I propose a similar name to the value it’s intended to replace – but with a prefix (such as EV) that makes it clear where the value is coming from.
If you wish to override the value for a project parameter with an SSIS environment variable, you do this on the “Configure” menu for the project.
Overriding the value for a package parameter with an SSIS environment variable is very similar – it’s just done on the Package’s “Configure” menu instead.
Note that SSIS environment variables can specifically provide values for parameters and connection managers. SSIS environment variables do not interact directly with the variables contained inside of SSIS packages.
More details on SSIS environment variables: http://technet.microsoft.com/en-us/library/hh213214.aspx
More details on SSIS environment variables: http://datachix.com/2011/12/02/sql-university-ssis-2012-deployments-lesson-two-part-b/
SSIS Project Configurations
With the new project deployment model, the concept of configurations is mostly gone. There is one exception, however. While in SQL Server Data Tools (Visual Studio), you can specify if any parameters are dependent upon a particular deployment configuration being selected.
This reference to “deployment configurations” are not the configurations you might be thinking of from previous versions of SSIS – rather, these are the deployment configurations available in the project properties. This deployment Configuration Manager has been available to manage different deployment scenarios for a long while now. The piece that is new is the ability to associate parameters to these configurations.
This functionality is only available in the Visual Studio development environment, and only applies to project & package parameters.
Hope that helps clarify some of the terminology in SSIS 2012!
You Might Also Like...
Parameterizing Connections and Values at Runtime Using SSIS Environment Variables