SQL Server 2012 Integration Service Catalogs Installation and Deployment

In previous post, we discussed of using integration service (SSIS) to render report in report server (SSRS) via report server web service to send out email with similar idea as data driven subscription. This post will be discussing the deployment of the SSIS project to the SQL Server 2012 new SSIS Catalog and some of its features.

There are multiple ways to create SSIS package e.g. with SQL Server Data Tools (SSDT, previously BIDS) from SQL Server feature selection installation or Visual Studio with SQL Server Data Tools - Business Intelligence for Visual Studio 2012 installed. In this example, the SSIS package is created with Visual Studio 2012 using integration service template (the interface and setting is almost the same with SSDT).

From the previous post, we already have a SSIS project. Keep in mind that since we are deploying to the new SSIS catalog, we will be deploying the package using project deployment model.

Integration Service Installation and Configuration
In this example, integration service is installed on the Integration Services Server (SQL Server instance that host the SSISDB database, more on SSISDB later). This Microsoft article layout the process and steps of installing the service. Although integration service is mainly used for monitoring or managing SSIS package deployed in msdb or file system through SSMS, it does not need to be running for managing SSIS package deployed to the new SSIS catalog. However, it is still required to be installed.

After the integration service is installed, now it is ready to create the SSIS catalog using SSMS. This Microsoft article list out the steps of creating SSIS catalog. As you see, creating the SSIS catalog is just a few simple steps. There is one thing you may need to pay attention. There is an option Enable automatic execution of Integration Services stored procedure at SQL Server startup. Selecting this option set the system stored procedure sp_ssis_startup to execute catalog.startup stored procedure in SSISDB database to perform certain maintenance of state of operations in SSISDB catalog. However, there is no SSMS user interface to revert this action after the SSIS catalog is created. To change this behavior, use sp_procoption to enable or disable this startup stored procedure. To verify current status of the SSIS startup stored procedure,

USE master;
GO

SELECT name, is_auto_executed
FROM sys.procedures
WHERE name = 'sp_ssis_startup';
GO

To enable the stored procedures to run at startup, use true on OptionValue parameter and false to disable.

USE master;
GO

--To enable startup stored procedure
EXEC sp_procoption 
 @ProcName = 'sp_ssis_startup',
 @OptionName = 'startup',
 @OptionValue = 'true';
GO

Configure SSIS project and Deployment
If your project is in package deployment model, you will need to convert it to project deployment model to deploy to SSIS catalog. More steps by steps detail could be found here at this Microsoft article.

Deployment to SSIS catalog is relatively simple in a few steps as layout in this Microsoft article. In general, open the SSIS project in Visual Studio or SQL Server Data Tools. 

- Right click the SSIS project, select Deploy Project

- In the Integration Services Deployment Wizard, select the destination server instance and its path. Create a new folder while selecting the path if needed.

- Review and deploy. 

That's it. Isn't that pretty straight forward? With that being said, make sure to apply standard policy and database practice (e.g. database file size and location, maintenance and backup) on the new SSISDB database. Be sure to understand how backup and restore work on SSIS catalogs. More details in this Microsoft article.

Here are a few features from SSIS catalog.

Environment, Environmental Variables and Parameters
Server environment, environment variables and parameters offer the capability to let user specify run time value for SSIS packages through server variables and parameters. This Microsoft article list the steps of how to create and map a server environment. This post at sqlblog by Jamie Thomson give a more visual steps by steps how to configure and use server environment and its variables.

Project Versioning
SSIS catalog maintain a list of version of the deployed projects. The catalog properties define how long these versioning is retained. It provides the capability to roll back or roll forward to other project version. The project could be exported as .ispac file and imported into SSDT or Visual Studio using Integration Services Import Project Wizard. The wizard also has the capability to import the deployed project at SSIS catalog directly to the SSDT or Visual Studio. Keep note that if the previous version of the project is desired to be exported, it requires the project to be reverted to that previous version (using SSMS) first.

Performance Statistics and Troubleshooting
The SSIS catalog provide various of catalog views, functions, and stored procedures to get performance statistics and information of execution. It also has the capability to add and query data taps in the package flow. This msdn article by Mat Masson shows an example how to use the views to identify SSIS performance issue. In addition, the SSIS catalog also shipped with some easy to use standard report on SSIS package execution and validation.

Also, don't forget to check out the awesome 30 days to SSIS series.