SQL Server SSIS : Create the SSIS Catalog which is must Before you can deploy the projects to the Integration Services

I would like to highlight that There have been some rather significant changes made to SSIS in SQL Server 2012 and One of which is project/package deployment. In SQL Server 2005 / 2008, one could easily deploy packages to the MSDB but In 2012, this has been changed. Before you can deploy anything, there has to be an integration services catalog.

Create the SSIS Catalog :

As soon as you are done with design and test packages part in SQL Server Data Tools, you can deploy the projects that contain the packages to an Integration Services server. Before you can deploy the projects to the Integration Services server, the server must contain the SSISDB catalog coz the installation program for SQL Server 2012 does not automatically create the catalog; you need to manually create the catalog by using the following instructions.

You can create the SSISDB catalog in SQL Server Management Studio.

To create the SSISDB catalog in SQL Server Management Studio:

1. Open SQL Server Management Studio.
2. Connect to the SQL Server Database Engine.
3. In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
4. Click Enable CLR Integration.

    The catalog uses CLR stored procedures.

5. Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSIS server instance is restarted.

Note:    The stored procedure performs maintenance of the state of operations for the SSISDB catalog. It fixes the status of any packages there were running if and when the SSIS server instance goes down.

6. Enter a password, and then click Ok.

The password protects the database master key that is used for encrypting the catalog data. Save the password in a secure location. It is recommended that you also back up the database master key. 

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)