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/
6 Responses
Ok, I am trying to put all the pieces together. I was able to extract my ispac from my Test server. Now I am trying to deploy it to Prod. Since I already have the folder structure in place the $SSISFolder.Create() is giving me grief.
Since the DeployProject is looking for $SSISFolder to be populated it is not letting it run.
Is there anyway to update the code to check if folder exists and create or move forward with the folder supplied.
I cannot seem to figure out the objects on this one.
It _sounds_ like you either want to do a Get-Item on the Prod folder which already exists. Or, if you created the folder with the .Create() method earlier in the PowerShell session, you might need to do a .Refresh() method on the SSIS Provider. See this post for more info about refreshing Providers: https://sqlvariant.com/2017/01/refreshing-the-sql-server-powershell-provider/
I’m guessing you just need to do the Get-Item on the SSIS Provider path to that existing folder. If I get a chance, I will write up a blog and demo how I have been doing this.
Thanks for asking, it’s a great question!
I’d like to be able to build/generate the ISPAC, then publish it to an SSIS Server w/ Project configuration, and set up the environment(s) to run it. We seem to be getting stopped on the “build an ISPAC” step more than anything else. The environments can be done with some work, though they’re still a bit tricky to code.
Peter,
Apologies for not responding to your question earlier. I ran off to try and figure out how to do what you were asking for, but neglected to even let you know that I was doing that
At this point my answer is: I’m not sure, but I think I may have found something. I will email some code I threw together to the email address you included with this comment.
Sir Im also trying to build the ISPAC so i can automate adding many dtsx packages. Do you have a way to do that for a lot of different dtsx.
I would like to deploy a package from serverA to serverB using PowerShell. This is a recent request that has been asked of me. With the method you mention above, I can use this, but I have to figure out how to Export the ispac from ServerA, then deploy to ServerB. Would be nice to use PowerShell to simulate the Integration Services Deployment Wizard.