Microsoft SQL Server 2012 is the latest version of the popular database management system. Microsoft has added a lot of new exciting features, in particular, to the SQL Server Integration Services (SSIS). Some of them are described in this article.
SQL Server 2012 brings the concept of parameters to SSIS. It adds a lot to the product usability.
The main purpose of parameters in SSIS is to organize the input for SSIS packages. So they can be treated as a package configuration.
Parameters can be declared at a project and/or package level and take 3 types of values: Design default value, Server default value, and Execution value.
| Value Name
|| Type of value
| Execution Value
|| A value that is assigned to a specific instance of package execution. This assignment overrides all other values, but applies to the specific instance of package execution.
| Server Value
|| A value assigned to the parameter within the scope of the project, after the project is deployed to the Integration Services server. This value overrides the design default.
|| Literal or Environment Variable Reference
| Design Value
|| A value assigned to the parameter when the project is created or edited in the SQL Server Data Tools. This value persists with the project.
To create a parameter you should fill in the following fields:
||A default value for the parameter is any is assigned at the design time. Also known as the design default.
||Sensitive values are encrypted in the catalog and appear as NULL value when viewed with Transact-SQL or SQL Server Management Studio.
||Require that a value, other than the design default, is specified before the package can execute.
SSIS parameters can be substituted into any expression in the package and they are immutable itself.
The parameters concept is more advanced than the legacy package configuration model that was hard to explain, understand and manage. Parameters also seem a good fit for all the ETL scenarios we may come across on a regular basis.
Build configurations provide a way to store multiple versions of a solution and project properties. You can quickly access and change the active configuration by using the Solution Configurations list on the Standard toolbar.
By default, projects created with MS Visual Studio include Debug and Release configurations. Debug configurations are automatically configured for application debugging, and Release configurations produce production release binaries. You can also create and edit your own custom solution and project configurations to meet the needs of your application.
Then you can easily bind parameter values to a build configuration settings. Just go to the parameters tab (Project or Package) and click the button shown below (‘Add Parameter to Configurations’).
Now you can choose parameters you want to associate with any number of your build configurations. You can enter a value for each parameter and configuration combination.
Build configurations allow setting up a custom start action, debug and start options, deploy and build settings. Switching between build configurations is as simple as selecting a value from a dropdown list. It’s hard to overestimate the usability and usefulness of this feature.
Integration Services Catalog and Environments
In the current release of Integration Services, you can deploy your projects to the Integration Services server. The Integration Services server enables you to manage packages, run packages and configure runtime values for packages using environments.
To deploy a project to the Integration Services Server you need to open the project in Visual Studio, and then from the ‘Project menu’ select ‘Deploy to launch the Integration Services Deployment Wizard’.
After that you should create a server environment to specify runtime values for packages contained in a project you’ve deployed to the Integration Services server. Then you can map the environment variables to parameters, for a specific package, for entry-point packages, or for all the packages in a given project
You should note that for a given execution, the package can execute only with values contained in a single server environment.
When you create an SQL Agent Job you can assign its execution to a particular environment. This makes environments extremely useful.
You can query the views for a list of server environments, environment references, and environment variables. You can also call stored procedures to add, delete, and modify environments, environment references, and environment variables.
So, our analysis shows that the concept of Integration Services Catalog, Environments and SSIS package parameters realized in SSIS 2012 brings MS SQL Server to a new level of usability and productivity in the ETL world and makes it much more handy to deal with.
About the Author
I work as a database developer for more than 9 years. I love my job and try to improve in this area, as well as learn new areas not associated with the databases. My life outside of work and my work are closely intertwined. I love to travel, and business trips has allowed me to discover new countries. I love cheerful companies, and the IT sphere gave me great friends. My hobby is table tennis and even at work I can play it.”