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

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

Download SSISProject containing:

1. DeploymentDemo SSIS Project
2. SimpleSSISDeploy.ps1

Step 2: Add Parameters.xml to the SSIS Project

Before SQL Server 2012, configuration data for SSIS package such as server names, database names, and SMTP servers can be stored in XML files, environment variables, or a separate configuration database. With the new Parameters feature of SSIS catalog, managing configuration data across your environments has never been so intuitive. Instead of  updating a database or a cryptic XML file, someone could simply login to the SSIS catalog via SSMS and configure parameter values manually. Although configuring an SSIS project directly in Production may seem convenient and give anyone a demi-God feeling, it is, however, very risky and is prone to human error.

To continuously integrate our SSIS project, we can automate the task of updating parameter values after the *.ispac is successfully deployed. To do that, we will use an input file, Parameters.xml, to tell our PowerShell script what value to set each parameters with depending on which environment it is deploying to. We will add this file within the SSIS Project itself, so that

1. It is under source control
2. Developers will be reminded to prepare it before deployment and
3. We have all the information we need to execute our SSIS project in one location

Enough of the intro, here’s exactly what you need to do:

1. Create a Parameters.xml file under the SSIS Project root folder (same folder as *.dtproj file). Paste the code below:

   
   <SSIS>
       <Development>
           <ProjectParameters>
               <Parameter Name="InitialCatalog">AdventureWorksDWH2012</Parameter>
               <Parameter Name="ServerName">SQLSSISDEV</Parameter>
           </ProjectParameters>
           <Packages>
               <Package Name="MainPackage.dtsx">
                   <Parameters>
                       <Parameter Name="SourceFolderRelativePath">\\Source\Folder\RelativePath</Parameter>
                       <Parameter Name="LogFileName">MainPackage.log</Parameter>
                   </Parameters>
               </Package>
           </Packages>
       </Development>
       <Acceptance>
         <ProjectParameters>
           <Parameter Name="InitialCatalog">AdventureWorksDWH2012</Parameter>
           <Parameter Name="ServerName">SQLSSISACC</Parameter>
         </ProjectParameters>
         <Packages>
           <Package Name="MainPackage.dtsx">
             <Parameters>
               <Parameter Name="SourceFolderRelativePath">\\Source\Folder\RelativePath</Parameter>
               <Parameter Name="LogFileName">MainPackage.log</Parameter>
             </Parameters>
           </Package>
         </Packages>
       </Acceptance>
       <Production>
         <ProjectParameters>
           <Parameter Name="InitialCatalog">AdventureWorksDWH2012</Parameter>
           <Parameter Name="ServerName">SQLSSISPRD</Parameter>
         </ProjectParameters>
         <Packages>
           <Package Name="MainPackage.dtsx">
             <Parameters>
               <Parameter Name="SourceFolderRelativePath">\\Source\Folder\RelativePath</Parameter>
               <Parameter Name="LogFileName">MainPackage.log</Parameter>
             </Parameters>
           </Package>
         </Packages>
       </Production>
   </SSIS>
   
   

2. Add Parameters.xml file as an existing item to the SSIS Project. It will automatically be placed under Miscellaneous folder.

   

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