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”)
[byte] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
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:
The best way I know to deploy SSIS projects to an SSIS Catalog in a container is using PowerShell.