SSIS Catalog: Part 1 - What is the Catalog?

SQL Server Integration Services projects in SQL 2012 will be deployed to the SSIS catalog. So as the first step you need to create SSIS Catalog.

SSIS Catalog is a SQL Server database named SSISDB.

In part 2 I will explain how to create SSISDB, but in this part I will just explain features of SSIS Catalog.

Folder Hierarchy

SSIS Catalog has it's own node in SSMS 

Catalog consists of folders, each folder may have one or more projects and environments. packages will resides inside the projects.

Project Based Deployment

this is another advantage of catalog; in SSIS 2008 or earlier packages were the core of deployment, but from 2012 project is the central configuration point for whole packages, we will see more details in feature parts.

Versioning

Version History will be stored at the time of project deployment, you can see list of all versions with date and time of deployment. ability to restore specific version is also available.

Validation

Previous versions of SSIS (2008R2 or earlier), were supported validation, but as a part in execution time. in 2012 with help of SSIS Catalog validation can be done as a separate step for packages or project.

Execution & Logging

There are a lot of improvements in execution of projects and logging which I will explain them in a separate blog post. there are four logging levels in SSIS 2012; None, Basic, Performance and Verbose. each of these logging modes will support number of events that will be logged into catalog database at the time of execution.

Logging can be enabled in the package execution GUI, and no extra work is needed during package development. and this is one of advantages of SSIS 2012 rather than previous versions.

Data Taps are new features in SSIS 2012 which provides ability to log whole data stream in one of data flow's data paths. and the best part of story is that you can add and remove data taps to a package at the time of execution.

Environments

When you deploy packages and projects to a server, you may want to run those projects in different environments. for example one for Test and another for Production. SSIS Catalog provided new repository for environments, where you can create multiple environments for example for test and production. in each environment you can assign different values to project parameters.

Environments can be assigned to projects or packages at the time of execution.

Reports

There are 4 main type of reports gathered by SSIS Catalog with help of logging modes; Execution, Validation, Operation and Connection reports. Reports are based on Reporting Services (SSRS) and shows details of execution or validation of packages and projects.

Permission

Permission can be applied on each folder, project, environment and operation. SQL Server roles can be selected and read, execute or other permissions can be granted or revoked for them. all permission details will be stored in tables in SSISDB catalog database. 

The new 2012 version of SSIS provided much more powerful permission style than earlier versions, and is much more suitable for enterprise real-world environments. 

in next blog posts I will get into each part and explain it more in details.