Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 1

Let’s start this by describing the scenario with which the approach described below will be useful to you:

1. You are using SQL Server 2012/2014
2. You have a separate server/instance for each staging environment (Development, Acceptance, Production, etc.)
3. You would like to automate SSIS project deployments to your target environments
4. You would like to automate managing SSIS package configurations (e.g. server names, database names, etc.) on all environments without using a separate configuration database

If you nod your way through the 4 points above, then take the time to read my solution below. It’s long, but I promise you will find some points mentioned helpful if not the entire thing.

Pre-requisites

I assume that you are familiar with the new Project Deployment Model feature of SSIS 2012/2014. You should also understand how Parameters are utilized with an SSIS Project. You should not be afraid of PowerShell and XML, for neither of them bite and both are very easy to learn. Note that I am not going to use the new Environment Variables feature because it assumes that all environments are deployed to a single server  – which is not the usual case for enterprises or any medium to large scale company for that matter.

The Solution in Summary

My solution has 3 components:

1. PowerShell script that deploys an *.ispac file to an SSIS Catalog and updates Project and Package Parameters
2. SSIS Project build configuration to store the SSIS Catalog information
3. Additional XML file within the SSIS project to store parameter values

Download SSISProject containing:

1. DeploymentDemo SSIS Project
2. SimpleSSISDeploy.ps1

Step 1: Setup the SSIS Project’s Build Configuration

We will store the target SSIS Catalog information in the SSIS Project itself so that our script can find on its own where to deploy the *.ispac file. To do that, open the SSIS project then..

1. In Solution Explorer, right-click on the Visual Studio solution and click Properties.
2. Solution Property Page dialog opens.
3. Under Configuration Properties, select Configuration.
4. Click Configuration Manager at the upper right corner of the window. Configuration Manager dialog opens.
5. Click the dropdown list under Active solution configuration.
6. Select New. New Solution Configuration dialog opens.
7. In the Name field, enter one of your environments. Check “Create new project configurations”. Click OK.
8. Repeat steps 4 – 6 until you have created solutions and project configurations for all  your environments.
9. Click Close. Click OK.

Now, we are ready to fill our build configuration for each environment.

1. In Solution Explorer, right-click on the SSIS project and click Properties. 
2. Propert Page dialog opens.
3. Under Configuration Properties, click Deployment. Configuration dropdown is now enabled.
4. Select one environment created earlier.
5. In the main pane under Deployment Model (Project), fill the Server Name and Server Project Path details. Click Apply.
6. Repeat steps 4 – 5 for all environments. Click OK to close the Property Pages window.

Finally, open the folder containing the SSIS project and locate .dtproj.user file. If you can’t see it, modify Folder Options to see the hidden files. The file should look like this:

<?xml version="1.0" encoding="utf-8"?>
<DataTransformationsUserConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <Configurations>
   <Configuration>
     <Name>Development</Name>
<options>
       <ServerName>SQLSSISDEV</ServerName>
       <PathOnServer>/SSISDB/LAB/DeploymentDemo</PathOnServer>
       <UserIDs />
       <UserPasswords />
       <OfflineMode>false</OfflineMode>
       <ProgressReporting>true</ProgressReporting>
       <ParameterConfigurationSensitiveValues />
     </Options>
   </Configuration>
   <Configuration>
     <Name>Acceptance</Name>
<options>
        <ServerName>SQLSSISACC</ServerName>
        <PathOnServer>/SSISDB/LAB/DeploymentDemo</PathOnServer>
        <UserIDs />
        <UserPasswords />
        <OfflineMode>false</OfflineMode>
        <ProgressReporting>true</ProgressReporting>
        <ParameterConfigurationSensitiveValues />
      </Options>
  </Configuration>
  <Configuration>
     <Name>Production</Name>
<options>
        <ServerName>SQLSSISPRD</ServerName>
        <PathOnServer>/SSISDB/LAB/DeploymentDemo</PathOnServer>
        <UserIDs />
        <UserPasswords />
        <OfflineMode>false</OfflineMode>
        <ProgressReporting>true</ProgressReporting>
        <ParameterConfigurationSensitiveValues />
      </Options>
  </Configuration>
 </Configurations>
</DataTransformationsUserConfiguration>

Next: Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 2