Powershell Create of the SSISDB Catalog

So when management gets this [insert explicative] idea to strip away my MSDN enterprise access, and I need to create an SSISDB catalog on my dev server- what the hell am I to do? Well, maybe not simple… I put on my PowerShell hack through hat. Shall we?

In your favorite SQL instance (we’ll just use localhost for my examples) start with making sure you have CLR ENABLED first:

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO

Do this in a query window in SSMS of course.

Now the PowerShell:

# 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"  

Write-Host "Connecting to server ..."  

# Create a connection to the server  
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  

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

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

The last part in “Provision a new SSIS Catalog” make sure you do a good password.

And that’s it…. So why am I doing this with PowerShell? No “DIS” on PowerShell, but it seems that the version I install SQL Server 2016 has this thing about connecting to Azure SSIS with creating a SSISDB Catalog, and since I no longer have access to MSDN (you knew MSDN was in this somewhere) I had to hack my way around getting a catalog created… Now yes, I could have done the Azure SQL SSISDB connect bah bah (I do have an enterprise Azure accounts), but you what? This same management does not want to spend money on Azure… YA! An Azure SQL SSISDB has a cost.

So there you go… Got to love this jumping around not having the tools to get stuff done.

~ScottGeek

Leave a Reply Cancel reply