In this new series of posts I will explain SSIS Catalog of Integration Services 2012.
Part 1 – What is the 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.
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.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Nice and Quick overview.