PowerShell How-To

Creating an SSIS Catalog with PowerShell

Here's how to deploy SQL Servers in an organization with custom business intelligence.

• By Adam Bertram
• 08/31/2017

When deploying SQL Servers and when you need to implement some of kind of business intelligence, chances are you're going to need a SQL Server Integration Services (SSIS) catalog. A SSIS catalog is a required piece for performing workflow functions, loading ETL packages and more. By using the Microsoft.SqlServer.Management.IntegrationServices.dll library, we can use PowerShell to automate the creation of an SSIS catalog along with folders inside.

Before we get started writing code, if you haven't already, you'll first need to install SQL Server Management Studio. This should get you the required libraries necessary to talk to the SQL Server. Once that's done, ensure you have at least PowerShell v4 installed. When you've met both of those prerequisities, you're ready to begin coding!

The first task to perform in this process is to ensure the .NET objects we'll be working with are available in our PowerShell session. This is due by loading the Microsoft.SqlServer.Management.IntegrationServices .NET assembly.

# Add-Type -Path $assemblyPath

Once this is done, we'll then go ahead and set some variables. Defining these variables aren't necessarily required but are easier to change if the script needs modified at any time. We'll create three variables; one for the name of the SQL server the catalog will be created on, a password for the catalog and the name of the catalog itself.

$sqlServerName = 'SQLSRV'
$secPassword = ConvertTo-SecureString -String 'p@$$w0rd' -AsPlainText -
Force $catalogName = 'SSISDB'

Next, we'll need to establish a connection to the SQL server. Below I'm defining a SQL connection string and passing that as an argument to the SqlConnection object. Your connection string may differ here.

$sqlConnectionString = "Data Source=$sqlServerName;Initial
Catalog=master;Integrat ed Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$sqlConnectionStri ng

Once you've got a System.Data.SqlClient.SqlConnection object captured, you will then pass that as an argument to the

Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices object. This essentially is telling your script that you're preparing to do something with SSIS. We haven't actually created anything yet.

$integrationServices = New-Object 'Microsoft.SqlServer.Management.IntegrationServi ces.IntegrationServices' $sqlConnection

Finally, we create a catalog object using the variables we previously created. The Microsoft.SqlServer.Management.IntegrationServices.Catalog object has a constructor that requires the Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices object we created earlier which is pointing to the required SQL server. We're also defining the catalog name here and finally passing the password in securestring format.

$catalog = New-Object 'Microsoft.SqlServer.Management.IntegrationServices.Catalog'
($integrationServices, $catalogName, $secPassword)

But, we still haven't actually created the catalog yet. We've just done the setup necessary to get to this point. To finally make a request to the SQL server and create the catalog, we'll simply need to call the
Create() method with no arguments to finish the job:

$catalog.Create()

If you take look at your SQL server, you should now see a catalog called SSDB created. You can also now make another connection in PowerShell and notice the catalog just created using the Catalogs property on your IntegrationServices object.

$integrationServices = New-Object 'Microsoft.SqlServer.Management.IntegrationServi
ces.IntegrationServices' $sqlConnection
$catalog = $integrationServices.Catalogs

To take things one step further, while we're here, we can also create a couple folders as well. Since we've just pulled the catalog from the SQL server in the correct object type, we can create a CatalogFolder type and call the Create() method on it to also create as many folders as we'd like!

$folderName = 'FOO'
$description = 'descriptionhere'
$folder = New-Object 
'Microsoft.SqlServer.Management.IntegrationServices.CatalogFo lder' ($catalog, $folderName, $description) $folder.Create()