SSIS Catalog: Part 2 – How to Create Catalog?

In previous post of SSIS Catalog Series, I explained what is the SSIS Catalog. In this post I will explain how to create SSIS Catalog.

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

In the "Create Catalog" window, you will see these options:

Enable CLR Integration

Catalog use CLR stored procedures, so you should enable CLR Integration to create the catalog.

Enable automatic execution of Integration Services stored procedure at SQL Server startup

This option will run below stored procedure;

sp_ssis_startup

This is a stored procedure in master database, which will run catalog.startup stored procedure in SSISDB catalog.

catalog.startup

this stored procedure exists in SSISDB catalog, and will find those operations which their process doesn’t exists, and will set their status as "ended unexpectedly".

By setting this option at the time of creating catalog, SQL Server will run sp_ssis_startup and as a result catalog.startup at every SQL Server startup and will set a status for operations that needs cleanup.

Name of the catalog database

at the time of writing this blog post, name of the catalog should be SSISDB and cannot be changed, but maybe this feature changes in the future.

Password

as SSIS catalog contains sensitive information and all data transfer package which are very important data, you need to provide a password to protect database master key.

Check Enable CLR Integration, enter a password and click OK and then SSISDB database will be created under SQL Server databases list.

After creating SSIS catalog it is good time to talk about catalog properties or configurations;

SSIS Catalog Properties

you can view catalog properties in these ways;

  • expand Integration Services Catalogs in SSMS and right click on SSISDB and from popup menu select Properties
  • select * from catalog.catalog_properties (on SSISDB database)

 

Encryption Algorithm Name

type of encryption algorithm that used to encrypt sensitive data.

supported values are: 

DES, TRIPLE_DES, TRIPLE_DES_3KEY, DESPX, AES_128, AES_192 and AES_256 (default)

Operations log

current size of operation log and retention period to clean operation log are in this category.

two main properties in operation log are:

Clean Log Periodically: if true then operation log will be clean at retention period, if false then operation log will remain.

Retention Period (days) : number of days to keep operation log in catalog.

Project Versions

two main properties of project versions are:

Maximum number of versions per project: defines the maximum number of versions which retained for each project

Periodically Remove Old Versions: if true, when number of versions exceed than the maximum number then old versions will remove. if false, old versions will be retained in the catalog. 

 

Changing Catalog Configurations

you can change catalog configuration in these ways:

  • using SSMS, right click on SSISDB catalog, select properties, change values
  • using catalog.catalog_configure stored procedure in SSISDB database.

catalog_configure command is simple to use with below syntax:

 

configure_catalog [ @property_name = ] property_name , [ @property_value = ] property_value

you can see exact name of each property from here

 

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 12 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, Power BI Summit, and Data Insight Summit.
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.

Leave a Reply