My friend Andy Leonard has written an excellent blog series on getting SSIS up & running in a Docker container. I would never have even tried to go down this road if not for his blog series.
Here’s a list of the posts in Andy’s blog series:
- 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 in a container (Part 4)
- shared how to deploy SSIS Projects to an SSIS Catalog in a container (Part 5)
- explored one option for Executing Packages in an SSIS Catalog in a Container (Part 6)
In Part 6 Andy showed that it is possible to execute a package that is within an SSIS Catalog inside a Docker container. However, he used the old dtexec command to do it. I don’t blame him for resorting to a tool that he already knows well, after already kicking down so many barriers to get SSIS deployed & running within a Docker container.
But I still felt that since Andy had already used PowerShell in so many steps throughout the blog series, the series really should include an example of how to execute an SSIS package with PowerShell. The only problem here was that I didn’t have many SSIS packages lying around in Docker containers for me to just try this.
Thankfully, Andy was very generous with his time last Thursday morning. We did a web meeting, Andy shared his screen and he let me tell him what commands I thought he should try. In the end, Andy was the one who figured out how to execute the SSIS package with PowerShell, I just showed him how to get there, despite the error messages he encountered.
At the end of our session, we found that the easiest way to execute an SSIS package in PowerShell was to navigate to the location of the package within the SSIS catalog, and then use the Get-Item cmdlet to grab the package and execute it. You can save some typing by navigating to he package you want in SSMS, and then right-click on it and choose “Start PowerShell”.
Note: The “.” tells the Get-Item cmdlet to grab the current item. You could also navigate to the folder the package is in, and then execute it using it’s DisplayName property.
(Get-Item . ).execute($false, $null)
After grabbing the package with Get-Item, we then use the .execute() method and pass it two parameters to run it.
The “10031” number you’re seeing displayed there is the Execution ID of the package. So when we see that, we know the package was successfully started.
Obviously, you have some easier options to do this in T-SQL or SSIS itself, but what if you’re already running some predecessor steps in PowerShell?
If you’re already doing work in PowerShell, and just happen to need to execute an SSIS package, this is a relatively easy option.
In addition, Andy and I figured out some other steps you could take to make executing SSIS in PowerShell even easier. Since Andy is the expert in SSIS, not to mention a better story teller than me, I will let him show folks which pieces make the most sense.
In the meantime, I hope this post inspires you to try some things that you didn’t even know were possible! ??
The post Execute SSIS Package from PowerShell first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.