This project has moved. For the latest updates, please go here.

Previous Configure SSIS Connectivity Manual Configuration Configure Notification Services Instance Next

Configure SSIS Catalog DB

FineBuild can create and configure the SSIS Catalog DB.

The SSIS Catalog DB is required by SSIS packages that run on SQL 2012 or above. It is used to store configuration and execution details.

Further information about the SSIS Catalog DB is at http://msdn.microsoft.com/en-gb/library/gg471509.aspx.

FineBuild Configure SSIS Catalog DB

The Configure SSIS Catalog DB processing relates to Process Id 5BGB and is controlled by the parameters below:

Parameter Build SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014 SQL2016
SetupSSISDB FULL N/A N/A N/A Yes Yes Yes
SetupSSISDB WORKSTATION N/A N/A N/A Yes Yes Yes
SetupSSISDB CLIENT N/A N/A N/A N/A N/A N/A

FineBuild also uses the following parameters to help configure the SSIS Catalog DB:

Parameter Default Value Description
SSISDB SSISDB Name of SSIS Caatalog DB
SSISPassword saPWD value Pasword for protecting the SSIS Catalog DB

The FineBuild configure SSIS Catalog DB includes the following: Top

Manual Configure SSIS Catalog DB

The following steps show what you would have to do for manual Configure SSIS Catalog DB processing. FineBuild does all of this work for you automatically.

Create the SSIS Catalog DB

The SSIS Catalog DB can be created via the SSMS GUI or via PowerShell. The example below shows the PowerShell statements used by FineBuild.
1) Run the following PowerShell commands, substituting the values given:
$HostServer The server where the install is running
$dbName /SSISDB value
$password /SSISPassword value

 [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")
 $ISNamespace       = "Microsoft.SqlServer.Management.IntegrationServices"

 $sqlConnectString  = "Data Source=$HostServer;Initial Catalog=master;Integrated Security=SSPI;"
 $sqlConnection     = New-Object System.Data.SqlClient.SqlConnection $sqlConnectString
 $SSISService       = New-Object $ISNamespace".IntegrationServices" $sqlConnection

 $catalog           = New-Object $ISNamespace".Catalog" ($SSISService, $dbName, $password)
 $catalog.Create()

Backup the SSIS Catalog DB Master Key

1) Using SQL Server Management Studio, run the following query to back up the SSIS Catalog DB Master Key, substituting the backup location specified for your server:
volbackup /VolBackup value
dirsql /DirSQL value
ssisdb /SSISDB value
ssispassword /SSISPassword value

BACKUP MASTER KEY TO FILE='volbackup:\dirsql\MSSQL.MSSQLSERVER.BACKUP\SystemDataBackup\ssisdbDBMasterKey.snk' ENCRYPTION BY PASSWORD='ssispassword'

Copyright FineBuild Team © 2015- 2016. License and Acknowledgements
Previous Configure SSIS Connectivity Top Configure Notification Services Instance Next

Last edited Oct 17, 2016 at 9:38 AM by EdVassie, version 2