I’ve spent what feels like a lifetime working on automating SSIS deployments, more specifically SSIS packages that have been created in Visual Studio that use the project deployment model. If your SSIS Project is using this model you’ll know that the output of a build is an .ispac file. But what is in an ispac?

Funnily enough, someone forwarded me a link to a page that covers some details about the ispac file format on MSDN. But before we get to much into that, let’s just satisfy ourselves with the fact that it is a project deployment file that has been created by using the ZipPackage class. And how can we determine that this is the case?

Firstly, the documentation specifies that an ispac is an Open Packaging Conventions (OPC) package. So with this knowledge we can alter the extension to “zip”, and then extract the content to verify the second point. I’m using the ispac available via the sql-server-samples repo on GitHub. We’ll go through each file and discuss their role in deployment of a SSIS Project:

Content_Types

The first file that we’re going to look at is the [Content_Types].xml file, and this is the file that confirms that the ZipPackage class is used. There’s an article here that is ten years old but is still valid (scroll down to the System.IO.Packaging INcludes Zip Support to read up on this.) This is because we know that the content_types file is part of the output when using the ZipPackage class to zip up a bunch of files into a .zip. The content_file contains both the extension and content type of the three other files that are included in the ispac:

Note that the content_types file does not specify the files, either in quantity or in content, other than the fact that they will contain xml.

So this file is not used by the deployment process, but is a result of when the ispac is created.

DTSX Files

Not much here to say about these as it’s pretty much a given that these would be included in an ispac file. A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations, that you built using Visual Studio. If you want to read up more on dtsx packages, then the documentation provided by Microsoft will tell you far more than I can.

Project Manifest File

The project manifest file contains data of the properties of the project, as well as it’s content. Open it up and you can see all manner of information. Below is the pointer to both the package and the connection managers.

Despite the fact that this project is fairly small, this manifest file is surprisingly large: 200+ lines, most of which is reserved for the element ProjectConnectionParameters. What is going on here is that each property of the connection manager is being separated into it’s own parameter. This is not the same as a project parameter (more on this later). Below is a copy of the manifest file with just one of the connection managers included.

It is possible to parameterise your connection mangers – you can turn them into project parameters or you can edit the property. An excellent article on SQLShack shows you how to edit the properties which we will go though below. Basically the properties of a connection manager saved as separate properties in the manifest file allow us to parameterise parts or the whole of the connection manager.1

If you’re wondering where this manifest file comes from, all you need to do is unload the dtproj file in Visual Studio. About 20 lines down from the top there is a “manifest” element, with content that is identical to the project manifest file:

This is how we are able to import from an ispac into Visual Studio using the “Integration Services Import Project Wizard”.

Project Param File

The project params file here is empty, and this is because our solution has no project parameters. As this is not a great help to anyone, I’m going to parameterise the server name property of one of the connection managers (to fully understand, see SQLShack), and then we’ll have something to view in the project params file. The project parameter is called “ServerInstance”, and sho ‘nuff after a compile the project.params file now has some content:

Each of the property elements under properties correspond to the UI used to create project parameters.

So now I’ve made this change, what has changed in the project.manifest file?

Nothing.

This surprised me, so then I looked in the connection manager

Line 7 contains the expression used to update the connection manager to the value of the project parameter. Interestingly, if you add a project parameter, it is only added to the project params file and not included in the manifest file. However if you add a package parameter it is included in the manifest under the dtsx package element.

Putting It All Together

It’s taken me a little while to disentangle the relationships between the types of parameters and how they are used by either dtsx packages or connection managers, but I’ve found it useful. Why I ended up looking into an ispac and trying to understand the relationship between the parameters is that in AssistDeploy I wanted to verify that the json file had the same number of project parameters. This is important because I did not want deployments to fail during the deployment because of a missing parameter, or even something like a typo.

In the Import-Json function, another function called Test-ProjectParamsMatch checks that the json file and the project params file match, in terms of number of entries, by comparing the name of the parameters. If the json has more, well then that’s not great, but it’s not going to cause a deployment to fail. if the json is missing a variable then a deployment is failed and the name of the parameter(s) that are missing are returned from the project.params file. So it’s not far easier to validate the json file pre-deployment.

Maybe at some point in the future I’ll include package parameters in the validation, but this is slightly more fiddly to complete. An alternative would be to just replace a package parameter with a project parameter and make use of the validation.

What I’d really like to see is the documentation updated to include the project parameters file and clarify that project parameters are not included in the project manifest. Another feature I’d love to see is the ability to get the values of the project parameters form the ispac without having to unpack, or any other strangeness, and update the values in memory. This feature is available in DacFX, and if I weren’t using environments for a project then the deployment process becomes infinitely more simpler.