Recently one of my friend who is BI expert gave an idea to use Integration Services Catalog to manage SSIS package in a more efficient way. He showed me some glimpse of that new feature and I have to say

It’s really useful and nice concept, I tried and implemented one of my environment and also tried how to migrate complete integration services catalog with all environment variables from one server to another and also implemented mirroring solution on Integration Services Catalog SSISDB to increase the availability of SSISDB database and availability of JOBS\SSIS.

Prior to SQL 2012 and SSISDB all packages are stored either in MSDB or in file system, also there is another way to store package in SSIS Package store.  SSIS Package Store is nothing but combination of SQL Server and File System deployment, as you can see when you connect to SSIS through SSMS: it looks like a store which has categorized its contents (packages) into different categories based on its manager’s (which is you, as the package developer) taste. So, don’t get it wrong as something different from the 2 types of package deployment (MSDB and File System).

SSIS packages are really just XML files, many organizations have used the “copy and configure” approach to deployments. In such cases, the packages are typically manually copied or copied using batch scripts to shared locations or MSDB database, where they’re executed. Configuration information is then stored in configuration files or special database tables meant only for storing SSIS configuration values.

The “copy and configure” approach can lead to problems. For example, a project where all the SSIS configuration parameters, including connection strings, were stored in a database table. As a result, whenever we restored a copy of the production database in the test environment, all the SSIS packages in the test environment would point to the production database

In an attempt to solve these types of problems, SSIS 2012 provides some new package deployment features, including the new SSISDB catalog and database. SSIS 2012 stores all the packages, projects, parameters, permissions, server properties, and operational history in the SSISDB database, bringing together all the “moving parts” for any SSIS deployment. You access the SSISDB database in SQL Server Management Studio (SSMS) by expanding the Databases node in Object Explorer.

What is Integration Services Catalog?

SSIS Catalog is a new repository model for Integration Services, which introduced from SQL Server 2012 and this new repository model comes with lot of features for developers, and database administrators.

The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations. The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history.

The project deployment model of SSIS 2012 no longer stores packages in the MSDB or on the file system. Instead once you configure Integration Services Catalog on server it separately creates a SSISDB database on the database server. The project deployment model also allows you to define different environments (DEV, CONS and PROD) to assign correct values for the parameters used in your packages according to the environment against they are executed.

Packages can be deployed to SSISDB and you can consider this as best practice. This feature will keep a deployment history of packages (like a very basic version control) so you can even rollback some revisions of your package. Now you don’t need XML or dedicated SQL tables to save your configurations. Now managing security is also very easy you can manage security through SQL Server because now everything can be handled via SQL Server Security instead of DCOM.

Advantages of Integration Services Catalog (SSISDB)?

• Packages can be directly deployed to SSISDB using SQL Server Data Tools for BI Studio. Packages keep a deployment history so you can even rollback some revisions of your package.
• You can manage security through SQL Server because now everything can be handled via SQL Server Security.
• Integration Services Catalog (SSISDB) comes with a new feature called Integration Services Dashboard, a report automatically built with report services template. Just click Integration Service Catalog and right click your packages to view “All Executions”.
• You can see very detailed Information about your packages including execution time.

• You can parameterize your Connection Manager or just parts of it.

Example: You can parameterize the server name and in your SSISDB you can create two environments (or more) called “CONS” and “PROD”. Then you can add variables to both of them and map them to the input parameter of your package. Main Advantage you can deploy a package to SSISDB and link to an environment and you don’t have to handle the connection strings by yourself.

• Main Advantage of the new model is the configuration. You don’t need XML or dedicated SQL tables to save your configurations. You can use input parameters and map them with environments defined on SQL server.

How to configure Integration Services Catalog (SSISDB)?

How packages are stored in various version of SQL Server?

• 2005 – stored in msdb.dbo.sysdtspackages90
• 2008 – stored in msdb.dbo.sysssispackages (I seem to recall 2008 RTM using a different table, or reused the 90 table but that got patched out)
• 2008 R2 – stored in msdb.dbo.sysssispackages
• 2012 (package deployment model) – stored in msdb.dbo.sysssispackages
• 2012 (project deployment model) – stored in SSISDB.catalog.packages*
• 2014 (package deployment model) – stored in msdb.dbo.sysssispackages
• 2014 (project deployment model) – stored in SSISDB.catalog.packages*

How to backup,restore or move SSISDB from one server to another?