In my last post, I showed how you can use the SSIS PowerShell Provider to execute an SSIS package with PowerShell. Of course, in order to execute that SSIS package, it has to get deployed first. In Part 5 of Andy Leonard’s “SSIS, Docker, and Windows Containers” series he used some PowerShell code from Matt Masson’s blog post to deploy an .ISPAC file to the SSIS catalog.
Now, Matt ( blog | twitter ) is certainly a “smart guy”, there is no doubt about that, but the question I wonder is: Is Matt Masson a “lazy guy”? Like me. 
You see Matt’s code example used 14 lines of code to deploy a single .ISPAC file to a single package catalog. That seemed like a little too much for me. I set out to see if I could streamline the steps a little and I was able to knock off ~4 lines of code. This might not seem like much, but for me, it made the deployment process a lot more understandable. I accomplished this simply by letting the SSIS PowerShell Provider do some of the work.
I also think this streamlining is crucial to show how this technique could be very useful in a DevOps or Scale-Out scenario.
In today’s world, people expect to be able to commit one change, then have the entire project re-deployed and automated tests run against it.
Just like in the post on executing an SSIS package with the SSIS Provider, I’ll use SSMS to save me some work and capture the path I need to get started.
Once again I’m working with one of Andy’s demo files which has an .ISPAC file named TestSSISProject.ispac for this deployment.
I plan to do a Pull Request to add an SSIS Provider example to the “Deploy an SSIS project with PowerShell” Docs page later today.
What’s next? The Docs pages show lots of capabilities that are already available to choose from. I think I want to figure out how to deploy a single SSIS package to an SSIS catalog using PowerShell. What do you want to be able to do with SSIS & PowerShell?
(Please comment with what you want to do, or cool things you’ve already done with SSIS & PowerShell)
Small update for this post: I received several requests to show how to Export an .ISPAC file from the SSIS Catalog, so I wrote a post about that, you can find the link here: https://sqlvariant.com/2019/05/exporting-an-ispac-from-the-ssis-catalog-via-the-ssis-powershell-provider/
The post Deploy SSIS Packages with PowerShell .ISPAC Deployment, using the SSIS Provider first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.
