SSIS Catalog: Part 1 – What is the Catalog?

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 thoughts on “SSIS Catalog: Part 1 – What is the Catalog?

Leave a Reply

%d bloggers like this: