Integration Service Catalog in SSIS 2012

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package).

The latter is certainly a headache for developers, particularly for me. We had to make use of Environment variable, SQL Table or XML config file for that. From a deployment perspective, SSIS packages are deployed to MSDB database for SQL Server deployment.

From SQL Server 2012 (SSIS 2012) there is a new feature called Integration Service Catalog. I am loving the feature. Let me explain why.

The feature provides the whole project deployment with project level variable and parameter deployment as well. In SSIS 2008, you have to deploy on 3 different environments (Dev, Test and Production). From SSIS 2012 there is new thing called Environment and just change the Environment on single deployment.

Let go through an example step by step.

1. Create Integration Service Catalog to Deploy SSIS Project using new feature of SSIS 2012

To deploy the project you need the Integration Service Catalog in the SQL Server instance. Please see the attached screen for reference.

Create Integration Service CatalogNote: You can not create more than one Integration Service Catalog. It is available in Database Engine service.

2. How to deploy SSIS project.

To See the deployment, we are here to target the Project parameters and new feature of Environment in Integration Service Catalog. Hence, lets see how to create project level parameter in SSIS project.

You can create parameter by expanding solution explorer and click on project parameter node. From where you can create parameter just like variables. There is some more fields like Required. Please see the below image.

Project ParameterAfter doing parameter and creating the SSIS project with multiple SSIS packages move to deployment. It's user friendly step have to follow.

Deployment to Integration Service CatalogNow you project are deployed and you can see it in Integration service catalog.

View of SSISDBNote: Benefit of this Parameters are introduced at Package and Project level.

3. How to create environment and Assign the environment to Project or Package.

To create environment go to the Integration Service Catalog and Expand the node till Environment.

Right click and Create Environment as like in image.

Create EnvironmentSave it and Open Property of newly created Environment. Create Variable(s) as required and give permission to use that.

General Tab of EnvEnv variablesEnv PermissionsAfter creating the environment the real picture come up of Package configuration (like in SSIS 2008).

To configure package go to Package, right click and open configure.

Package ConfigFor image you can see, you have different levels of configuration (Project level, solution level and package level).

Now assign value to parameter as per the environment (Development, Test or Production).

and Select Environment in Reference tab to Map environment variables with Package or project parameters.

Environment SelectionBy these way you can use the new functionality. Hope you leaned something new and enjoyed.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Comments (1)

Hi PatelAlpesh,

Is there anything to do from scripts task with environment variables. My company follow standard procedure first in script task which check all the connection and serverName and so and so fort then data flow task.

There environment variables xml file don't have much info
1.generated person details
2. ConfigurationType, Path from variables
3. ServerName Path="\Package.Variables[User::Environment].Properties[Value]" ValueType="String">

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.