Integration Service Catalog in SSIS 2012

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.

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

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

Now you project are deployed and you can see it in Integration service catalog.

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

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

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

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

By these way you can use the new functionality. Hope you leaned something new and enjoyed.