An Overview of SSIS Projects in SQL Server 2012

One of the new and exciting features of SSIS in SQL Server 2012 is the SSIS Project and project deployment model that is now available. The new project deployment model includes a new option for deployment as well as a bunch of new features such as shared connections, properties, environments and versioning. This post will introduce this new model and highlighting some of the various features and functions that are available.

Create the Integration Services Catalog

Before we can get started the first time its necessary to create the SSIS Catalog. To install the catalog on an instance, right click the ?Integration Services Catalog? folder on the SQL Server 2012 instance and click ?Create Catalog??. Once the dialog launches, you have the option to automatically schedule an IS job that periodically cleans up the catalog database. You are also prompted to specify a  password to protect the database key. Create your password and then click ?Ok?.


Create Catalog Dialog


After the catalog is created, you will notice two instances of it within Management Studio. The first instance is available through a new database called SSISDB that has been added to the server instance. There are numerous T-SQL commands that are available to manage the catalog database. The second instance of the catalog is available in the object explorer under the Integration Services Catalogs.


SSIS Catalogs in Management Studio

A few important notes to consider:

  • Don?t forget to backup your master key, once you have created a database master password.
  • In order to use IS Projects, CLR Integration is required to be enabled on the server instance.

Project Connection Managers

One of the big misses in prior versions of SSIS was the inability to share connection managers across packages. This has been fixed in the new project model  with the introduction of Project Connection Managers. The connection managers are defined in the same way connection managers in prior versions were established. The difference is that they are now available in every package in the project and can be configured and managed from a single point.

Solution Explorer
Package Connection Managers

The second major benefit is that using project level connection managers allows us to share cache connections across package boundaries.  This is a big win in the performance arena as it will no longer be necessary to rebuild cache connections from package to package.

There are a couple important things that should be pointed out about these connections. The first is that expressions are not supported for connection managers at the project level. Expressions still work as normal at the package level, however. Secondly, connections can be promoted or demoted from a package connection to a project connection and vice versa. This feature is available on the context menu as ?Convert to [Project/Package] Connection? of the connection in the package connection managers window.

Project Parameters

Another new concept in the SSIS Project deployment model is the concept of parameters. Parameters can be scoped at either the package or project level. Properties that are scoped at the project level are available in every package within the project using the following syntax:


Options also exist for setting parameter values as they can be set either at design-time, at the server/catalog level, or they can be configured at runtime.  Parameters can be configured and stored in the SSIS Catalog. This feature allows for yet another option to manage runtime configurations without having to worry about securing configuration file or storing connection strings to a configuration database..


Project Parameter Dialog

The parameter?s dialog should look very familiar as it is similar to variables window with a few exceptions. The first thing you will notice is that there is a flag to mark the parameter as sensitive. If you set this value to true, the parameter value will be encrypted in the SSIS catalog and will appear as NULL when queried either through T-SQL or Management Studio. The second difference is the Required flag. This flag allows the developer to require that a value other than the design default to be supplied before the package can be executed.

The last item to note in our overview of parameters is the ability to parameterize task within the SSIS workspace. This is a nifty feature that allows you to map task values to parameters to allow them to be configured once the project is deployed. You access this dialog by right-clicking on the task in the designer and choosing ?Parameterize??. The dialog allows you to select a property of the task and then either map it to an existing parameter or to create a new parameter (with the appropriate scope-level) all within the same unit of work.


Parameterize Task Dialog


Deploying your project to the server is primarily accomplished by using the deployment wizard. The wizard has two main steps necessary to deploy your project to the SSISDB catalog. The first is selecting a source, whether is be a project deployment  file (*.ispac) or an IS catalog and the second is specifying the destination. In addition to telling the wizard the host server you must either specify an existing path or create a new folder to host your project.

Deployment Wizard ? Source
Deployment Wizard ? Destination


Environments are new and are designed to act as containers for variables. Each SSIS project can have multiple environments defined but only a single environment can be referenced at the time the package executes. Once the environment is define and referenced in the project you can map the environment variables to a parameter value.

Create a new environment
Define an environment variable
Reference the environment
Map the environment variable to a parameter


Versioning is another freebie that?s included when you use the project deployment model. Each time you deploy your project a new version is created. History can be configured at the server level and if you chose to install the automatic IS procedures when you created the catalog the clean-up of out of date versions will be handled for you. Rollbacks are also a breeze with the ?Restore to Selected Version? button on the version dialog.


Project Versions Dialog


The final feature of the new SSIS Project Deployment Model that I want to point out is the reporting capabilities that are baked-in. While the out of the box you reports are marginally useful the real bang for your reporting buck can be found in the reporting pack available out on CodePlex ( – courtesy of Jamie Thomson). I would urge you to download it and check it out if for no other reason than to see the data that is available for your project within the SSIS Catalog.

Built-In Overview Report
Built-In Execution Report
Built-In Messages Report

Till Next Time!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s