Creating an SSISDB catalog
In this recipe, we will create an SSISDB
catalog.
Getting ready
To create an SSISDB
catalog, we must first enable SQLCLR
on the instance. Log in to SQL Server Management Studio, and use the system stored procedure sp_configure
to enable CLR. Execute the following T-SQL script:
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
How to do it...
These are the steps required to create SSISDB
programmatically:
Open PowerShell ISE as an administrator.
Import the SQLPS module as follows:
Import-Module SQLPS -DisableNameChecking
Load the
IntegrationServices
assembly as follows:Add-Type -AssemblyName "Microsoft.SqlServer.Management.IntegrationServices, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add the following script and run it:
$instanceName = "localhost" $connectionString = "Data Source=$instanceName;Initial Catalog=master;Integrated Security=SSPI" $conn = New-Object System.Data.SqlClient.SqlConnection $connectionString ...