Configure SSIS Catalog DB - SQL-FineBuild/Common GitHub Wiki

Previous Configure SSIS Connectivity	Manual Configuration	Configure Notification Services Instance Next

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 https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog.

FineBuild Configure SSIS Catalog DB

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

SQL Version	Parameter	FULL Build	WORKSTATION Build	CLIENT Build
SQL2019	/SetupSSISDB:	Yes	Yes	N/A
SQL2017	/SetupSSISDB:	Yes	Yes	N/A
SQL2016	/SetupSSISDB:	Yes	Yes	N/A
SQL2014	/SetupSSISDB:	Yes	Yes	N/A
SQL2012	/SetupSSISDB:	Yes	Yes	N/A
SQL2008R2	/SetupSSISDB:	Yes	Yes	N/A
SQL2008	/SetupSSISDB:	Yes	Yes	N/A
SQL2005	/SetupSSISDB:	Yes	Yes	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:

• Create the SSIS Catalog DB
• Backup the SSIS Catalog DB Master Key

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:

   Option	Value
   $HostServer	The server where the install is running
   $dbName	/SSISDB: value
   $password	/SSISPassword: value

   [Reflection.Assembly](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

   Substitute the backup location specified for your server:

   Option	Value
   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 - 2020. License and Acknowledgements

Previous Configure SSIS Connectivity	Top	Configure Notification Services Instance Next