SSIS Catalog: Part 2 – How to Create Catalog?

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

 

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.

Leave a Reply