PowerShell script to automate the SSIS Catalog creation

Below PowerShell script will create the SSIS Catalog, if it is not already there.

 # Create a connection to the server

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=.;Database=master;Integrated Security=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

# Enable CLR

$SqlCmd.CommandText = $("exec sp_configure 'Clr enabled', 1
reconfigure ")

$SqlCmd.Connection = $SqlConnection

$SqlConnection.Open()

$SqlCmd.ExecuteScalar()


# Check SSISDB is exists

$SqlCmdDB = New-Object System.Data.SqlClient.SqlCommand

$SqlCmdDB.CommandText = $("SELECT ISNULL(DB_ID('SSISDB'),0)")

$SqlCmdDB.Connection = $SqlConnection

$DB_ID = $SqlCmdDB.ExecuteScalar()

# Create the SSISDB if it not exists

IF($DB_ID -eq 0)
{

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $SqlConnection

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "YourPassword")

$catalog.Create()

}

$SqlConnection.Close()