SSIS, Docker, and Windows Containers, Part 5 – Deploying to the SSIS Catalog

In the previous five posts I:

examined how to install Docker for Windows, pull a test image, search for a SQL Server on Windows image, install and interact with it (Part 0)
shared why I want to do all this (Part 1)
shared one way to execute SSIS on a container in Part 2
shared a failed attempt to add an SSIS Catalog to a SQL Server-on-Windows container in Part 3
shared a successful attempt to create an SSIS Catalog (Part 4)

I Have Not Yet Figured Out…

I do not (yet) know how to deploy to a container using the Integration Services Deployment Wizard. This bugs me and I want to figure out some way to do it because deployment testing is critical for SSIS.
I have not figured out how to execute SSIS packages in an SSIS Catalog in a container using the SSMS Object Explorer’s Integration Services Catalogs node.

What I Have Figured Out

I know how to deploy SSIS packages to an SSIS Catalog (the topic of this post); and
I know how to execute them from the SSIS Catalog (the topic of the next post) – both using PowerShell.

Deploying an SSIS Project to the SSIS Catalog

Returning to Matt Masson’s PowerShell script – combined with the docker volume added earlier – I have a means to deploy an SSIS Project to the SSIS Catalog in the container.

I copied an ISPAC file into my D:\docker\install directory:

I can access this ISPAC file from within the container:

From Matt’s post, I execute the following slightly-modified PowerShell script:

# from https://www.mattmasson.com/2012/06/publish-to-ssis-catalog-using-powershell/
$ProjectFilePath = “C:\install\TestSSISProject.ispac”
$ProjectName = “TestSSISProject”
$FolderName = “Test”
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”) | Out-Null;
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”
$sqlConnectionString = “Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection
$SSISCatalog = “SSISDB”
$catalog = $integrationServices.Catalogs[$SSISCatalog]
$folder = New-Object $ISNamespace”.CatalogFolder” ($catalog, $FolderName, “Folder description”)
$folder.Create()
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

When I execute this script, my results appear as shown here:

View in SSMS

If successful, I can view the deployment in SQL Server Management Studio (SSMS) after refreshing the SSISDB node in SSMS Object Explorer’s Integration Services Catalogs node: