SSISDB Catalog – SQL Server 2012 best feature to improve SSIS project deployment

What is Catalog? SSIS Catalog is a new repository model for Integration Services, which introduced from SQL Server 2012. This new repository model brings a lot of abilities for developers, testers and administrators. 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. Name of SSIS catalog database is SSISDB. SSIS Catalog has it's own node in SSMS: 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. Versioning:

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 four logging level is 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. Note: There are a lot of improvements in execution of projects and logging which I can share in some technical session.

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. Report Layout

To Create SSIS Catalog you first need to install SQL Server 2012 and after installation open SSMS and right click on "Integration Services Catalogs" node and select "Create Catalog"

See you soon with new post.