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.
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[version]\") 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[contains(@Path,'MyDatabase')]/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
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)
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.
Pingback: WebDeploy Quick Tip: Deploy without installing the Agent Service – DOTNET CATCH