Creating an SSIS Catalog with PowerShell
Here's how to deploy SQL Servers in an organization with custom business intelligence.
- By Adam Bertram
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
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:
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
$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()
Adam Bertram is an independent consultant, technical writer, trainer and presenter. Adam specializes in consulting and evangelizing all things IT automation mainly focused around Windows PowerShell. Adam is a Microsoft Windows PowerShell MVP, 2015 powershell.org PowerShell hero and has numerous Microsoft IT pro certifications. He is a writer, trainer and presenter and authors IT pro course content for Pluralsight. He is also a regular contributor to numerous print and online publications and presents at various user groups and conferences. You can find Adam at adamtheautomator.com or on Twitter at @adbertram.