Continuous Deployment of SSIS packages from Visual Studio with TFS

I’ve written a few blog posts about Continuous Integration and database development using Visual Studio 2013 and Team Foundation Server.

The posts can be read here.

It all culminated when I did a session at SQL Konferenz in Darmstadt last week. My session was really well visited (approx 65 people when the session ended) and it once again proved me that this is a very relevant topic.

After the presentation ended a few people came up to ask some questions. Personally this is the part that I prefer during a session. Proving that you have captured the audience attention and that they want to hear some more about the subjects.

One of the questions that I was asked by a lady which name I forgot to ask was :

“Is it possible to do Continuous Deployment of SSIS packages or SSAS models in the same way (after successful build and check in)”.

My answer was a loud and clear : “Yes” even though I have never tried it for real. This post is  a description of the process of deploying SSIS packages after each build.

First of all MSBuild is not compatible with SSIS deployment so you cannot use this mechanism. It would be nice if it were possible but right it isn’t.

Using DTutil

DTutil appeared to be the right choice to me at the beginning. Specially because I was looping over all the packages in a nice way. My script looked like that :

for %I in (*.dtsx) do “C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe” /FILE “%I” /COPY DTS;”DeploymentPOC\%~nI” /DESTSERVER localhost

The DTUtil commands details on Books Online can be found here.

There was no problem deploying to a file using DTUtil with this script :

for %I in (*.dtsx) do “C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe” /FILE “%I” /COPY FILE;“c:\temp\%~nI.dtsx”

Where I use the FILE parameter in stead of the DTS parameter there are no issues. But when using the DTS parameter (indicating that we need to deploy to SSISDB catalog) I get the following error.

But I wasn’t successful at deploying to the SSIDB Catalog. To recap, deploying to MSDB (old-fashioned) or to a folder was not a problem. But SSIDB Catalog didn’t work for me.

The reason for that being that DTUtil can only be use for deploying packages in the package depl

But when working with SSIS in SQL Server Development Tools with SQL Server 2012 and 2014 you deploy using a tool called the Integration Services Deployment Wizard which SQL Server Development Tools calls internally

Integration Services Deployment Wizard

This wizard is principally invoked from Visual Studio but is also a executable with some parameters that you can use for deployment.

The ISDeploymentWizard.exe file can be found there :

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\ (for SQL Server 2014).

There is some parameters that allow you to control what and how to deploy.  Run ISDeploymentWizard.exe /? from a command prompt for a list of available parameters.

Deploying with PowerShell

Although the wizard is the preferred method for deploying form within SSDT, there are some other ways to do it. Some more documented than others. Books Online explains how certain public stored procedure from SSIS are exposed to be used for deployment purposes. There are even some examples available there.

This will be the subject of an upcoming blog post.

Deploying with C#

Another way of doing it is using PowerShell. A third way is by using the part of the SSIS Management Objects that are exposed as SMO classes that you can use in your code. Specially the CatalogFolder.DeployProject method is interesting in this case.

The build definition

Once again we need to build a custom build definition for Team Foundation Server and once againg we need to configure it the right way to allow Continuous Deployment. The key setting here being the Trigger pane and the Continuous Integration setting:

I am not sure that the dev team for SSDT had SSIS in mente when developing this Trigger settings. In that case Continuous Integration is a poor choice of name.

In the Process tab of the Build definition we need to add a path to a command file containing the deployment script. The deployment file itself is a binary file with the .ispac suffix (Integration Services Deployment File). It is generated upon every successful build by SQL Server Data Tools.

The Integration Services Deployment File

It is an implementation of an Open Package Convention (OPC) Package. Which means that it must contain 2 parts :

• A project manifest
• Zero or one more parts that contain an IS Package

So an ispac file is nothing else than a compressed archive. Add a .zip suffix after the file and you will be able to crack it open. Doing so will expose the content of the file.

The most interesting part is the @Project.Manifest which contains a lot of metadata in XML format about the packages, the connections and the project properties. First and foremost it indicates the protection level of the project but also a project version, a project date and a creator. This is also the place to find the connection strings used in the packages. So if you ever receive an .ispac file and don’t have access to the Integration Services project you can easily figure out where the data connections are.

Another interesting part of the archive is the Project.params file which is also an XML file containing all the parameters you have defined at the project level.

If you are interested in all the gory details of the .ispac specification I invite you to take a closer looks at MSDN where you will also find the PDF document with all the schemas and explanation.

The Deployment script

There is not much to it.

Let’s have a look at the parameters:

/Silent : It does exactly what it says and prevents the big ugly dialog window to popup

/SourcePath: Indicates where the project source is. The address of the .ispac file which is always located in the bin folder of the project folder. Under the configuration you are using for the project (usually Development).

/DestinationServer: Since we are deploying to the SSIS catalog (why would you want to deploy anywhere else?) this is the name of the SQL Server instance where your SSIDB Catalog reside.

/DestinationPath: The absolute location of the packages in the SSISDB Catalog structure.

In my case it looks like this :

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe”
/Silent /SourcePath:“<project path>\DeploymentPOC.ispac”
/DestinationServer:“localhost”
/DestinationPath:“/SSISDB/<project folder>/DeploymentPOC”

Next time you check your project in, the script should kick in and publish your files to the SSIDB Catalog that you specified in the /DestinationPath parameter.

Happy deployment !!

Tags: dtutil, SQL Server Integration Services, ssis, Team Foundation Server