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 '[email protected]$$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 a 20-year veteran of IT. He's an automation engineer, blogger, consultant, freelance writer, Pluralsight course author and content marketing advisor to multiple technology companies. Adam also founded the popular TechSnips e-learning platform. He mainly focuses on DevOps, system management and automation technologies, as well as various cloud platforms mostly in the Microsoft space. He is a Microsoft Cloud and Datacenter Management MVP who absorbs knowledge from the IT field and explains it in an easy-to-understand fashion. Catch up on Adam's articles at adamtheautomator.com, connect on LinkedIn or follow him on Twitter at @adbertram or the TechSnips Twitter account @techsnips_io.