Notes on SQL

Although introduced in SQL 2012 I’ve never encountered this feature before, as all sites where I have previously worked have started with much earlier versions, and the old habits of implementing SSIS packages and jobs have just continued.

Now I am working for a company that is starting from afresh – no previous installations to worry about and have started with SQL 2012.

Prior to 2012 SSIS packages were stored in the MSDB of an Integration Services server. It was also (and still is) possible of course to just run the dtsx files located in a local folder but I was never a fan of this – it could get messy.

From SQL 2012 a new feature appears within SSMS – ‘Integration Services Catalogs’. Within that is the SSISDB and within that is a folder structure (specified by yourself) and the SSIS packages. All information required to execute the SSIS package is stored within the related database – connection details, passwords, parameters etc., all encrypted for security.

Creating SSISDB

Initially the entry ‘Integration Services Catalog’ has no entries:

Right-click on the ‘Integration Services Catalog’ and select ‘Create Catalog…’, this will start the wizard for this process (it is possible to perform all of this with PowerShell, but that is beyond my abilities – or desires).

Because the IS Catalog relies upon CLR you must check the ‘Enable CLR Integration’ checkbox. It is unchecked by default, which makes no sense unless MS just want to draw your attention to the fact it requires enabling CLR.
The option ‘Enable automatic execution of Integration services stored procedure at SQL startup’ is not mandatory but it is recommended. The purpose of this option is to run a stored procedure that corrects the status of any packages that were running should the server shut down at the time.

A password is also required, which protects the database master key used to encrypting the data within the catalog. Keep this password safe and as an extra precaution backup the database Master Key. Once the wizard has completed you can backup the Master Key with the code below (change the file location to suit your requirements):

use SSISDB;
go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<Code you entered in the Wizard>'; 

BACKUP MASTER KEY TO FILE = 'c:\temp\exportedSSISDBmasterkey' 
    ENCRYPTION BY PASSWORD = '<New password>'; 
GO

Having entered the password into the wizard, click on ‘OK’ and the SSISDB will be created under ‘Integration Service Catalogs’:

And a new database appears, named SSISDB of course. The mdf and ldf files are placed into the default folders.

Adding an SSIS Package to the SSISDB will be covered in the next article …..