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?.

SSISProject-0025

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.

SSISProject-0024

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.

SSISProject-0026
Solution Explorer
SSISProject-0027
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:

@[$<<Project/Package>>::<<ParameterName>>]

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..

SSISProject-0028

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.

SSISProject-0023

Parameterize Task Dialog

Deployment

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.

SSISProject-0020
Deployment Wizard ? Source
SSISProject-0019
Deployment Wizard ? Destination

Environments

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.

SSISProject-0001
Create a new environment
SSISProject-0030
Define an environment variable
SSISProject-0031
Reference the environment
SSISProject-0029
Map the environment variable to a parameter

Versioning

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.

SSISProject-0000

Project Versions Dialog

Reporting 

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 (http://ssisreportingpack.codeplex.com/ – 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.

SSISProject-0006
Built-In Overview Report
SSISProject-0004
Built-In Execution Report
SSISProject-0005
Built-In Messages Report

Till Next Time!

Chris

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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