In my last blog post I showed that using the SSIS Provider could be an easier option for deploying an .ISPAC file, vs. the PowerShell script method shown in the official documentation. I very quickly received two comment on that post asking:
- How do you use PowerShell to extract an .ISPAC from the SSIS Catalog?
- How do you automate generating the .ISPAC file from an SSIS Project in the first place?
How do you use PowerShell to extract an .ISPAC from the SSIS Catalog?
For the first one, I wasn’t sure if it was possible, but I assumed it should be possible. The way to find out is to start grabbing objects and piping them to the Get-Member cmdlet. First, I tried the Packages themselves, then I tried the folders. No luck on either of them. Then I went poking around the Catalog in SSMS and right-clicking on the various nodes to see which options popped up. Finally, when I got to the Project itself I was given an option to Export.
You’ll notice with the code below, that we’re able to extract that Project-object into a variable named $Proj, but when we pipe that to the Get-Member cmdlet we do not see a method called Export.
$Proj = Get-Item 'SQLSERVER:SSISlocalhostSQL2017CatalogsSSISDBFoldersProvider Solution2ProjectsTestSSISProject' $Proj | Get-Member -MemberType Methods
As it turns out, it’s a good thing I wrote the importing article first. Otherwise, I probably wouldn’t have spotted the method we need. There is a method named GetBytes that I totally skipped over at first. When I couldn’t find anything I started thinking back through the steps I did to import the .ISPAC file. I remembered this piece of code [System.IO.File]::ReadAllBytes($ProjectFilePath) converted the .ISPAC file into a very long string of byte numbers.
<# Dont run this piece of code, you will hate me. #>
$Proj.GetProjectBytes()
So, I tried it. And I saw bytes! And about 9 minutes later, I was able to do something with my PowerShell window again
In PowerShell cmdlets are supposed to be in pairs:
- If there’s a Get there should be a Set
- If there’s an Add there should be a Remove
- If there’s a Read there should be a Write
Since we used ReadAllBytes
method to pull the file in off of the hard drive, it stands to reason that we would use a WriteAllBytes method to push the data back out to a file on the hard drive. I did a quick search and sure enough there was a method by that name. After a quick look at an example of how to use it, I came up with the following code.
[byte[]] $ProjBytes = $Proj.GetProjectBytes()
[System.IO.File]::WriteAllBytes('C:tempASSISPrj.ispac',$ProjBytes)This created the .ISPAC file on my hard drive in a flash, which was great! Of course, who knows if what’s in that file is usable? It could be a big jumbled mass in there for all I know? To find out what I had done, first I renamed the file to .zip and looked inside.
Looks good to me.
Next I used the code from the previous post to deploy the .ISPAC file to a different folder within the same SSIS Catalog. Success again! But still, I don’t know if it’s really usable or not, so I run the package. Great news! The package that is supposed to succeed, succeed. The package that is supposed to fail, failed. Now the neither package really did anything, it didn’t move any data or anything. But at this point I’m fairly convinced that I can now export an .ISPAC file with the SSIS PowerShell Provider.
Quick thing to note:
One this I figured out [the hard way] during this, is that you can deploy the .ISPAC to a folder with a different name, but the Project must still have the same name. It’s embedded within the .ISPAC file, and apparently, it gets checked on deployment. You may have already known this, but I didn’t (or if I did know, I forgot)
How do you automate generating the .ISPAC file from an SSIS Project in the first place?
Now to answer the second question, I used the phone instant-message-a-friend option. I asked Mat the Mad M-Man Masson ( blog | twitter ) what to do? He told me to give this GitHub project a try. If you’re looking to automate building a Visual Studio SSIS project (dtproj) into an .ISPAC file, you should give this project a try.
The project includes New-SsisDeploymentPackage, “A PowerShell Cmdlet that builds a deployment package from a Visual Studio Project File”. If you do, try it out, please let me know how it works out for you?
