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;
This is a stored procedure in master database, which will run catalog.startup stored procedure in SSISDB catalog.
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.
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)
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.
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.