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:
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:\).
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:
Declare @execution_id bigint
@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
DECLARE @var0 sql_variant = N’D:\Processed’
@execution_id, @object_type=20, @parameter_name=N’ProcessedFilePath’, @parameter_value=@var0
DECLARE @var1 sql_variant = N’D:\Rejected’
@execution_id, @object_type=20, @parameter_name=N’RejectedFilePath’, @parameter_value=@var1
DECLARE @var2 sql_variant = N’D:\dummy.txt’
@execution_id, @object_type=30, @parameter_name=N’FullFileName’, @parameter_value=@var2
DECLARE @var3 smallint = 1
@execution_id, @object_type=50, @parameter_name=N’LOGGING_LEVEL’, @parameter_value=@var3
EXEC [SSISDB].[catalog].[start_execution] @execution_id