As a MSBI developer, I always enjoys while deploying of SSRS reports and SSAS cubes but not in case of SSIS packages deployment because we have project level single click deployment in SSRS and SSAS, here our life is so easy but I always feel difficult with  SSIS packages legacy deployment and management of SSIS packages. There we need to deploy SSIS packages individually either in file system or system database MSDB
Good News for SSIS Developers is, in SQL server 2012 Microsoft introduced SSIS Package Deployment Model i.e. single click project deployment. Also it introduced new specific database for only SSIS Package Deployment called SSISDB. Now in this topic I cover what it is SSIS Package Deployment Model and how to deploy your SSIS packages to SSISDB

In SQL Server 2012, new major changes in SSIS are project deployment and managing the configurations. By this major introductions, we can stop completely using traditional configurations like Parent package, XML configurations and environment variable package configuration. In new project deployment, it added features called Project parameter and Package parameters, Environments, Environment variables and Environment references

Project Deployment Model

As we discussed above, Project Deployment Model is the new deployment model for SSIS projects. If we open new SSIS solution and create new packages under this solutions, by default all SSIS projects are project deployment model only. If we want to change back to old Legacy Deployment model, we can also migrate to it.

When we create a project with this model and build our solution, an .ispac will create under bin/Development folder. This .ispac will include all our packages and parameters

Integration Service Catalog

In previous deployment models we used to store packages either in File system or system Database MSDB. In SSIS 2012, Integration Services catalog introduced to store all deployed packages, parameters and logging of deployed packages executions (like when package started ,When package got failed ) in SQL server database and Integration Services catalog used Encrypt the data to store sensitive data .

Integration Services catalog also stores multiple versions of deployed packages. In Integration Services catalog all deployed SSIS projects, packages, parameters, environments and all package executions (operational) history will be stored in the SSISDB database .Suppose if we have 100 SSIS packages and all 100 packages runs every few minutes , In SSISDB it will store all historical information and hence our SSISDB database growth will increase day by day rapidly  you could see how the database storing all the historical information would grow exponentially. So we need to configure SSISDB as per our requirement

Below are some basic configurations on SSISDB to log Operational information

  • Retention Period in Days – Defaults to 365
  • Default Logging Level – Defaults to Basic
  • Maximum Number of Old Versions Per Project – Defaults to 10
  • Whether to Periodically Remove Old Versions – Defaults to True

Project Parameters and Package Parameters

In SSIS 2012, we create package parameters and Project parameters apart from variables in packages. This parameters can be access inside packages and can set values of package components like connection strings, variable values.

The only difference between project parameters and package parameters is the scope access. A package parameter can be access inside particular package only but the project parameter can access global to the project. Parameters can also store sensitive data and stored in encrypted form in the catalog.

Difference between parameter and Variable is once package execution starts parameter never change but variable value can be change while executing package

By three types we can assign value to parameter

1. Static value given to parameter in SSIS project or package
2. Catalog level value will pass to SSIS project/package parameter
3. Environment variable will pass to Catalog level and then overwrites SSIS project/package parameter

Environments and Environment variables

Again concept of environments and environment variables are
introduced in SSIS 2012. As part of automation SSIS comes with this new feature. With this environments and environment variables, even layman can easily deploy SSIS packages in any environment without any difficult.

On top level, an environment is placeholder for environment variables. These environment variables values are Static values . In Integration Services catalog we can create any number of environment and we can create environment variables inside environment. Environment is like ‘DEV’,’TEST’ and ‘PROD’.

For deployed projects we can tag only one environment at a time and assign environment variables to project parameters /Package parameters. Due to that environment variable values will overwrite project parameters /Package parameters

Demonstrate Project Deployment Model

To demonstrate this SSIS Deployment model, now I will create one simple SSIS project and I will show them how to deploy in Integration Services Catalog. And also, I will create DEV and QA environments and environment variables inside DEV and QA environments. Finally I will map environment to project and explain how environment variables have to configure. Based on environment we choose variable values inside that environment will pass to the project. For example if I choose DEV environment, then DEV environment variables will overwrite values of project /Package Parameters inside

Following steps will cover as part of Integration Services Catalog
                         1.     Configure Integration Services Catalog

    1. Configure Integration Services Catalog

     To make use of new features of Integration Services, we need to create an Integration Services Catalog in SQL Server instance. Only single Catalog we can able to create in single SQL Server instance.
     Integration Services Catalog uses common language runtime (CLR) Stored procedures. But common language runtime (CLR) integration feature is off by default . To enable we need to use sp_configure 

sp_configure 'show advanced options', 1;
sp_configure 'clr enabled', 1;
In create catalog ,in SQL Server Management studio,right click on node of Integration services and click on Create Catalog option.

Next, it will popup one window and shows database name as SSISDB. There we need to give Password. Why we need to give password because in SSISDB, it will store sensitive data. So to encrypt sensitive data we need password


After integration Services Catalog is created, we can see SSISDB in SQL Server. In Integration Services Catalog, it saves all deployed packages and its operational logging data in SQL Server SSISDB and in this database we can able to see predefined tables ,views and system procedures to store all deployed packages information.

In Integration Services Catalog on we can deploy many projects deployed and also we create several folders to easy navigation and management. In Below example I have created FirstProject folder for my project

We can create a folder by just right click in SSISDB node and choose Create Folder. Next it will open the Create Folder window as shown below; give a name for the folder to create :

After we create any folder in Integration Services Catalog, we can able to see two sub folders under newly created folder.
Projects: A place holder for deployed projects inside Folder
Environments: A placed holder for environments like DEV and QA


Kiran Reddy A

Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL

Get Free Email Updates to your Inbox!

Powered by Blogger.


Total Pageviews

Copyright © Journey to MSBI World With Kiran Reddy | Blogger Templates | Designed By Code Nirvana