Deploying Multiple SSIS Projects via PowerShell

When there are several SSIS projects with packages in a SQL Server Database or Data Warehouse development, automated deployments as part of Continuous Integration can get tricky. Nat Sundar describes how he created a Deployment script that is intended to provision a Data Warehouse for System Integrated testing (SIT).

Overview

This article describes a way of using PowerShell to deploy multiple SSIS projects as part of a database or data warehouse deployment. It contains code snippets to illustrate some of the details, and so you can amend it for similar requirements if necessary.

System Integration Test

This set of scripts were developed in order to automate the process of setting up a test cell to verify the build via a System integration test. It was done to ensure that all the dependencies between several SSIS projects for the Data Warehouse systems were all accounted for.

Before each test run, the entire Data Warehouse system was provisioned afresh within the SIT test environment.

Solution Overview

In this solution, I will explain how to develop a PowerShell script to deploy multiple SSIS projects / packages. You can use the script straight away to deploy the SSIS artefacts in a test environment. I recommend that you to read all the steps detailed in the article before you do so, and understand the consequence. If required, you may modify the script based on usage. This script has six steps to complete.

What is expected from the script ?

The deployment script will read the Server configuration details (XML) and then deploy the Integration Services Project Deployment (ISPAC) files to the target SSIS server. The server configuration file is an XML file which contains server names for several test environments. This script can be used to deploy the ISPAC files to other test environments such as System Unit Test, System Integration test and Regression Test environments.

Solution in Detail

The screenshot below represents the steps involved at very high level.

Assumptions

We’ll assume that the reader is already aware of the SSIS build automation which is detailed here, and that the reader has got fair understanding of PowerShell.

Step 1: Reading Server Details from a Configuration file

It is always a good idea to maintain the server details in a separate configuration file (an XML). This configuration file can keep the details that are specific to each environment. A sample XML configuration file has been created for demonstration purpose.

The file maintains the details for SIT, System test and regression test environments. For each environment, we are maintaining such details as the Environment name, Server Instance Name and Source File Location.

There are various advantages to maintaining the details in a separate file. The configuration file can be easily updated if there are any changes to the name of the server or any environment specific values. Also there is no need to change the code to update the environment-specific values.

This PowerShell script will read an XML configuration file and, based on the given target environment, will print the values.

Step 2: Enabling CLR in the server

You must enable the CLR on the SQL Server before you create the catalog on the SSIS server. The following section of the PowerShell script will do this.

Step 3: Creating SSIS Catalog in SQL Server

As we are setting up a System Integration Test environment, we should first drop the existing SSIS catalog so as to ensure that the existing projects, packages and environment details are deleted completely.

However if we are planning to reuse the same script for other environments, we might want to comment out the relevant sections. This will help to keep the existing Catalog and deploy the SSIS projects.

This script has three variables that are used to store the values of the password for SQL Server and the SSIS Catalog. The SSIS catalog stores sensitive information such as the package password in the SSIS Catalog. Therefore we must protect the Catalog with a password.

This script will first check whether the CLR is enabled in the server. If not, then the PowerShell script will enable the CLR: Then it will connect to the SQL server. Once the connection has been established, the script will check for the presence of Catalogues in the server.

If the catalog is not available in the first place, then the script will continue to create a catalog on the target server, using the password supplied.

If the catalog is available, then it will raise a warning and it will drop the entire catalog. This means that all the available SSIS projects, packages and environments will be completely removed. You must decide whether this is appropriate in your environment. Once the catalog has been dropped, then it will recreate the Catalog.

Script to create SSIS catalog

The screenshot below below confirms that the SSIS catalog has been created successfully.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\SSIS_Catalog_Creation.png

Step 4: Reading ISPAC files from folder

The scripts is written with the assumption that the Data Warehouse solution has many SSIS projects and these are stored in many folders. The screenshot below shows several SSIS projects, each in its own folder.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\SSIS_Folders.png

In addition, each SSIS package has two package parameters called ServerName and SourceFileLocation.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\Package_parameter.png

Listing SSIS Project files

Now we need to find the name and location of the ISPAC file in the folder. This PowerShell script will loop through the given folder and will list out the name of the ISPAC file and its full path. In later stage this code snippet will be integrated into the main code.

Script to print the project files in a given folder

Creating SSIS Projects in the Catalog

In this step, we will be integrating the code from the previous step. The PowerShell script will help us to drop the existing catalog and create a new catalog. Then the script will search for the SSIS project files in the given folder. Once found, the name of the SSIS project file will be used to create a folder in the SSIS Catalog. As each and every project need to be deployed to a separate folder this setup will help.

In addition, the full location of the folder will also be stored in a separate variable. This folder location will be used to derive the full path for the ISPAC file in the next step.

Script to read ISPAC files and to create a folders in the SSIS catalog

The screenshot below confirms that folder has been created for each SSIS project.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\SSIS_Folders_Created.png

Step 5: Deploy ISPAC files to SSIS catalog

This step integrates all the steps mentioned above (Step 1, Step 2 & Step3)

In this step the script will read the available ISPAC file for each SSIS project. The ISPAC file will be read into a Byte array variable and then the file will be deployed into the SSIS Catalog.

Script to deploy ISPAC files to the SSIS catalog

The screenshot below confirms that all the SSIS projects have been deployed successfully.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\SSIS_Projects_Created.png

Step 6: Setting up Environment for SSIS catalog

Creating an Environment for the SSIS Project

This step will help us to create an environment for each SSIS Project and will assign the environment to the project. This consists of three parts. In the first part, we will be creating an environment for the SSIS project. In the second part, a set of Environment variables will be added to the Environment. In the last part, the SSIS project variable will be referenced to the Environment variable.

An environment object can be created with the help of the EnvironmentInfo in the SSIS Integration assembly by passing the Folder name, Environment name and description.

Adding variables to the environment

Once an environment has been created, then variables can be added to it. A variable can be added by accessing the Variables.Add method by passing the arguments we’ll mention soon.

The Add methods accept variable name, datatype, default value, sensitivity and description as parameters.

Referencing the environment variables to SSIS Project

The last step will create an environment with variables. Now in this step, we will see a procedure to add the environment to the SSIS project.

First the SSIS project has to be referenced to a variable using the Projects method in the catalog, by passing the SSIS project name. Now the environment can be linked by accessing the References.Add method and by passing the EnvironmentName.

Setting up package parameters using environment name

Now we can make use of the environment variables to link with the package parameters.

The SSIS package has to be referenced to a local variable. Now the parameters method on the SSIS package can be used to link the Package parameter to the environment variable.

Script to create SSIS environment and setting up variables

From the screenshot below, you’ll see that the SSIS projects have been deployed with the SSIS packages and the “SIT” environment has been created for each project.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\SSIS_Environments.png

This screenshot confirms that the environment “SIT” has been created with two variables.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\5_Environment_Properties.png

In addition the “SIT” environment has been referenced in the SSIS project.

C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SIT Deployment Script\Iamges\5_Environment_Project_reference.png

Enhancements to PowerShell script

The values of the key information such as the Server name and the data file location has been hardcoded in the deployment script. Now the deployment script will read the config. XML for key values. Based on the Target test Environment value (SIT, TST or RST), it will lookup the key values in the XML config file. The key values will be stored in few variables that can be used while creating a SSIS environment in the script.

Script to integrate XML config file with the PowerShell script

Command line execution and passing parameters

Usually the release manager, or whoever holds responsibility for the release, always prefers to deploy SSIS projects from the command line. Because the deployment script has been prepared in PowerShell, it is possible to execute the script from the command line.

In the previous script, we have hard-coded a number of variables, to make things simpler, but for the final version of the script, we can make the change to accept parameters for these values.

Script to run PowerShell script from command line with parameters

This script accepts parameters for TargetEnvironment, ConfigPath, SSISCatalogPassword and SSIS folder location. The PowerShell script can be executed in two ways with parameters.

By Position

In the first method, the parameter values can be provided with a space in between each parameter. Based on their position, the corresponding parameter values will be set and the script will be executed.

By Parameters

In this method, we can specify the parameter name and the value together. This is the recommended method as it is easy enough to understand the parameter and values.

Logging PowerShell script output:

The supplied script will display the deployment progress in the console. This is very helpful, if you are planning to integrate the script with the CI tools. As the CI tools usually maintain their own log , the PowerShell script output information will be available on the CI log for analysis and investigation.

However if you are planning to use the script for unattended deployment then the script can be enhanced with additional logging. The same script has been modified to incorporate a logging to capture the details in a text file. 

A function “Write-Log” has been added to the script to accept a string and write the details to a logfile. This function will be called during the deployment and the progress information will be supplied as a parameter.

This is the updated script

Points to remember

This script is intended to provision a Data Warehouse for System Integrated testing (SIT). For this we usually drop any existing SSIS Projects and deploy all the SSIS projects with packages and environments. If the requirement is similar then this script can be used straight away to meet these requirements.

However, if you don’t want to drop existing SSIS projects, you may comment/remove the code to drop the existing catalog.

In addition you can check whether the specific SSIS project is available and delete the project if exists.

You should always delete the existing SSIS projects for a clean deployment. This will help us to investigate and debug deployment-related issues more easily.

Summary

You should check this script in a sandbox environment. This will help you to understand the script in detail, and you can modify the script to make it work better in your real test environment.

If you use a Continuous Integration tool within your organisation, you can easily integrate this script to add it to an automated deployment. In addition, if you have an automated test framework for SSIS projects, then you can deploy and run the test framework to validate the deployment.

Reference

SSIS Deployment Reference from Matt Masson ( http://www.mattmasson.com/2012/06/publish-to-ssis-catalog-using-powershell/)

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue