Setting up an SSISDB Catalog

With the introduction of SQL Server 2012, project deployments became available. Project deployments contrast package deployment, which was the only way SSIS packages could be deployed to SSIS servers. Package deployment model SSIS solutions could be configured by creating configuration files, command shell environment variables, and user defined variables inside the packages.

The project deployment model introduced project parameters and package parameters, as well as environment variable definitions that reside in the SSISDB catalog itself, as opposed to the OS environment variables. Also, parameters defined in a project or package, can easily be mapped to this new SSISDB environment values. Note that more than one environment can be defined, such as Development, Acceptance, and Production.

The project deployment model is now the recommended way of creating SSIS solutions, and it is also the default deployment model when a new Integration Services solution is created in Visual Studio 2012 and above.

So, we want to use this new project deployment model and create an SSISDB catalog on our SSIS server. How do we do that? It’s very simple, yet take care of making a backup of the master key and database encryption keys when you do. The following example shows what steps need to be taken to create a secure SSISDB catalog.

In SQL Server Management Studio, navigate to the ‘Integration Services Catalogs’ folder and right-click on it. Select ‘Create Catalog…’. The ‘Create Catalog’ dialog opens.

Figure 1: Creating an SSISDB catalog.

Figure 2: the ‘Create Catalg’ dialog box

After the password is entered, an SSISDB database master key is created on the server. This master key is used for encrypting the packages, and is protected by the Service Master Key. The Service Master Key is created during SQL Server setup, and uses Windows Data Protection API for its own security.

We now need to make an encrypted and password protected backup file of the master key by executing the following SQL query:

SQL
USE SSISDB
BACKUP MASTER KEY TO FILE ‘C:\SSISDBkey’
ENCRYPTION BY PASSWORD ‘P@ssw0rd’

If recovery is needed, load the master key again using the following SQL (assuming that the master key file is named stored ‘SSISDBKey’ on and stored on C:\).

SQL
USE SSISDB
RESTORE MASTER KEY FROM FILE ‘C:\SSISDBkey’
DECRYPTION BY PASSWORD ‘P@ssw0rd’
ENCRYPTION BY PASSWORD ‘NewP@ssw0rd’ — FORCE — optionally add the FORCE clause
/*** NOTE: Use the FORCE option only if the master key is irretrievable or if decryption fails. ***/

Not only you now have created an SSISDB catalog, you also created a regular SSIDB database. They are one and the same thing, it’s just that the catalog provides SSIS specific commands.

Figure 3: Creating an SSISDB catalog also creates an SSISDB database

Interestingly, when you apply SSISDB catalog commands, such as ‘Execute…’, instead of pressing OK after the parameters are assigned, you can also create a T-SQL script that will do the same thing.

Figure 4: Executing a package in the SSISDB catalog

Figure 5: Generating a T-SQL script instead of directly executing the package after parameters are assigned.

For instance, the following T-SQL was created for the sample package execution:

SQL
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’Package.dtsx’, @execution_id=@execution_id OUTPUT, @folder_name=N’Test’, @project_name=N’Integration Services Project1′, @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant N’D:\Processed’


EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id, @object_type=20, @parameter_name=N’ProcessedFilePath’, @parameter_value=@var0
DECLARE @var1 sql_variant = N’D:\Rejected’

EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id, @object_type=20, @parameter_name=N’RejectedFilePath’, @parameter_value=@var1

DECLARE @var2 sql_variant N’D:\dummy.txt’
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id, @object_type=30, @parameter_name=N’FullFileName’, @parameter_value=@var2

DECLARE @var3 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id, @object_type=50, @parameter_name=N’LOGGING_LEVEL’, @parameter_value=@var3

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Advertisements

One thought on “Setting up an SSISDB Catalog

  1. Richard Torrone

    Arno, this was great! So simple and sweet and straightforward – you saved me a lot of time. I’m gonna call my sister and tell her to blow a kiss in the air while she calls out “Arno” (If she sees your picture I won’t have to pay her). That’s how impressed I am! Thanks a million!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s