Deploy SSIS Packages with MSDeploy

February 13, 2017 / Uncategorized / 1 Comment

SQL Server Integration Services (SSIS) is a common and useful tool for many large enterprises but they rarely have an automated deployment strategy.  The common misconception is SSIS deployments are hard or not possible to automate.  Like everything else we have attempted to automate the deployment of, MSDeploy can handle SSIS packages too. 

In this post, we will review how to create a MSDeploy package for an SSIS package and deploy it. Note, this is by no means the only way to deploy SSIS packages but if you are already using and invested in MSDeploy, it may be a good option for you.

MSDeploy Package Creation

In our use case, we have the DST package (*.dtsx files) and a configuration file (*.dtsConfig) that need to be copied to a remote file share.  SQL Server is configured to pull the SSIS package from this location.  The MSDeployAllTheThings.AppDeploy is the Nuget package we will use to create the MSDeploy package and deploy the files to a remote server.

Depending on the type of Visual Studio project you use to organize/manage your SSIS packages, you may be able to add the Nuget package to your existing project.  I’ve found most database-related project templates in Visual Studio do not allow you to add Nuget packages to your project (not sure why).  The alternative is to add a class library project to the solution to add the Nuget package to and create the deployment package.

image

The AppDeploy package will add several files to the project.  Because we are using a seperate project we will need to pull the DTS packages and config file from the other project in the *.app.targets file:

  <Target Name="AfterBuild" >
    <ItemGroup>
      <DTSXFiles Include="..\SSISMSDeployPackage\*.dtsx" />
      <DTSConfigFiles Include="..\SSISMSDeployPackage\*.dtsConfig" />
    </ItemGroup>
    <Copy
        SourceFiles="@(DTSXFiles)"
        DestinationFolder="$(OutDir)"
        />
    <Copy
        SourceFiles="@(DTSConfigFiles)"
        DestinationFolder="$(OutDir)"
        />
  </Target>

In this case we are pulling all the files and placing them in the root of the Deployment project output folder to package.  You could also create a sub-directory structure if needed.

You can also customized the file path for the target server by setting the DestinationFilePath MSBuild property:

<PropertyGroup>    
    <!-- Read the readme.txt file in the AppDeploy package folder (typically at "..\packages\AppDeploy&#91;version&#93;\") for help using app deploy. -->
    <!--<BatchCommandVariables>var1=value1;var2=value2</BatchCommandVariables>-->
    <DestinationFilePath>d$\SSIS_Packages\MyCustomPath</DestinationFilePath>
</PropertyGroup>

The configuration file has environment specific information so next we need to setup parameterization.  First, add a new parameter in the parameters.xml file:

<parameters>
  <parameter name="DTSConfig-MyDatabaseConnection"
            description="Specifies the connection string for MyDatabase."
            defaultValue="DefaultConnection">
    <parameterEntry
         kind="XmlFile"
         scope="\\*.\.dtsConfig"
         match="//Configuration&#91;contains(@Path,'MyDatabase')&#93;/ConfiguredValue/text()" />
  </parameter>
</parameters>

Here you can see XPath is used to select the ConfigurationValue text we want to parameterize.  You can set the environment specific values in the appropriate SetParameters files:

<parameters>
  <setParameter
      name="DTSConfig-MyDatabaseConnection"
      value="LocalConnectionString" />
</parameters>

Use the following MSBuild command to create the MSDeploy package:

>"c:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild" /p:DeployOnBuild=true /p:ImportParametersFiles=true

image

Deploying the Package

With AppDeploy, its very easy to deploy the resulting MSDeploy package by specifying the target server and setParameters file as arguments to the *.appDeploy.cmd:

>Deployment.appDeploy.cmd localhost -setParamFile:setParameters.DEV.xml
Adding variables to pre/post scripts
Starting deployment of Deployment.appDeploy.cmd ...

>"C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -verb:sync -source:package="Deployment.appDeploy.package.zip" -dest:dirpath="\\localhost\d$\SSIS_Packages\MyCustomPath",computername=localhost,username=,password= -preSync:runCommand="preSync.changed.bat",waitInterval=1000 -postSync:runCommand="postSync.changed.bat",waitInterval=1000 -setParamFile:setParameters.DEV.xml

Performing '-preSync'...
Info: Using ID 'dffee64a-b656-4411-8e36-5575a663fe83' for connections to the remote server.
Info: Using ID '2f2092b9-b0d1-4a3a-aa3f-eed7c0f1a1e6' for connections to the remote server.
Info: Updating runCommand (preSync.changed.bat).
Info: C:\Windows\system32>set configuration=Debug
Info: C:\Windows\system32>set server=localhost
Info: C:\Windows\system32>set appName=Deployment
Info: C:\Windows\system32>set destinationFilePath=d$\SSIS_Packages\MyCustomPath

Warning: The process 'C:\Windows\system32\cmd.exe' (command line '') exited with code '0x0'.
Completed '-preSync'.

Info: Using ID 'f19077a1-fd91-4e4d-990b-9b5cfbbe08d8' for connections to the remote server.
Info: Adding directory (\\localhost\d$\SSIS_Packages\MyCustomPath\deployment).
Info: Using ID '36747bbd-4451-42e4-b1fa-e9c81329e2d3' for connections to the remote server.
Info: Adding file (\\localhost\d$\SSIS_Packages\MyCustomPath\deployment\postSync.bat).
Info: Adding file (\\localhost\d$\SSIS_Packages\MyCustomPath\deployment\preSync.bat).
Info: Adding file (\\localhost\d$\SSIS_Packages\MyCustomPath\Deployment.dll).
Info: Adding file (\\localhost\d$\SSIS_Packages\MyCustomPath\Deployment.pdb).
Info: Adding file (\\localhost\d$\SSIS_Packages\MyCustomPath\Package.dtsx).
Info: Adding file (\\localhost\d$\SSIS_Packages\MyCustomPath\SSISMSDeployPackage.dtsConfig).

Performing '-postSync'...
Info: Using ID 'b5418b03-aca5-4fed-b9cd-d46e09f900be' for connections to the remote server.
Info: Using ID '73b46144-faf6-4263-875b-abecdaa6c543' for connections to the remote server.
Info: Updating runCommand (postSync.changed.bat).
Info: C:\Windows\system32>set configuration=Debug
Info: C:\Windows\system32>set server=localhost
Info: C:\Windows\system32>set appName=Deployment
Info: C:\Windows\system32>set destinationFilePath=d$\SSIS_Packages\MyCustomPath

Warning: The process 'C:\Windows\system32\cmd.exe' (command line '') exited with code '0x0'.
Completed '-postSync'.

Total changes: 7 (7 added, 0 deleted, 0 updated, 0 parameters changed, 13382 bytes copied)

image

As you can see the SSIS package and config are placed in the target folder.  There are a few additional files from our deployment project but these can be ignored.

All the code for this post is posted on Github if you need to reference it:

https://github.com/rschiefer/SSISMSDeployPackage

I hope this helps others that wish to automate the deployment of their SSIS packages.  If you have a better solution or questions/comments regarding this post please write a comment below.

1 thought on “Deploy SSIS Packages with MSDeploy”

  1. Pingback: WebDeploy Quick Tip: Deploy without installing the Agent Service – DOTNET CATCH

Leave a Reply