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