SSIS Catalog and Project Deployment with PowerShell

This may be my shortest blog post ever as I get ready to sign off from work for the next three weeks. But before I do, I wanted to share a quick script to automate deployment for SSIS 2012 (and 2014). I can’t take full credit for this script as the foundation was taken from Matt Masson post over on MSDN (HERE).

Overview

A brief summary of the script below:

1. Check for the catalog and create it if it doesn’t exist
2. Checks for a project folder in the catalog, creating it if it doesn’t exist
3. Deploys the project from the ISPAC file
4. Creates an environment (again if it doesn’t already exist) in the project folder and then adds a reference to the Project
5. Adds a variable programmatically to the Environment folder
6. Configures a package parameter within the project to use the environment variable

Without further ado, the script is provided below:

Script

 $ServerName = "localhost"
$SSISCatalog = "SSISDB"
$CatalogPwd = "P@ssw0rd1"

$ProjectFilePath = "C:\Dev\SSISDeploymentDemo\SSISDeploymentDemo\bin\Development\SSISDeploymentDemo.ispac"
$ProjectName = "SSISDeploymentDemo"
$FolderName = "Deployment Demo"
$EnvironmentName = "Microsoft"

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=$ServerName;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

$integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection

$catalog = $integrationServices.Catalogs[$SSICatalog]

# Create the Integration Services object if it does not exist
if (!$catalog) {
    # Provision a new SSIS Catalog
    Write-Host "Creating SSIS Catalog ..."
    $catalog = New-Object "$ISNamespace.Catalog" ($integrationServices, $SSISCatalog, $CatalogPwd)
    $catalog.Create()
}

$folder = $catalog.Folders[$FolderName]

if (!$folder)
{
    #Create a folder in SSISDB
    Write-Host "Creating Folder ..."
    $folder = New-Object "$ISNamespace.CatalogFolder" ($catalog, $FolderName, $FolderName)            
    $folder.Create()  
}

# Read the project file, and deploy it to the folder
Write-Host "Deploying Project ..."
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

$environment = $folder.Environments[$EnvironmentName]

if (!$environment)
{
    Write-Host "Creating environment ..." 
    $environment = New-Object "$ISNamespace.EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    $environment.Create()            
}

$project = $folder.Projects[$ProjectName]
$ref = $project.References[$EnvironmentName, $folder.Name]

if (!$ref)
{
    # making project refer to this environment
    Write-Host "Adding environment reference to project ..."
    $project.References.Add($EnvironmentName, $folder.Name)
    $project.Alter() 
}

# Adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
$customerID = $environment.Variables["CustomerID"];

if (!$customerID)
{
    Write-Host "Adding environment variables ..." 
    $environment.Variables.Add(
        “CustomerID”, 
        [System.TypeCode]::String, "MSFT", $false, "Customer ID")
    $environment.Alter()
    $customerID = $environment.Variables["CustomerID"];
}


$package = $project.Packages["Package.dtsx"]
$package.Parameters["CustomerID"].Set(
    [Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, 
    $customerID.Name)            
$package.Alter()  

Wrap-Up

I hope this script is useful in helping you automate your SSIS deployment so that they are as pain free as possible. Feel free to drop any question of comments you may have below.

Until next time and wishing you all a Happy Holidays!

Chris

Comments

• Anonymous
  March 05, 2015
  Nice Article. Matt Masson's script was not as complete and left me looking for something closer to production ready!
• Anonymous
  March 29, 2015
  The comment has been removed
• Anonymous
  April 15, 2015
  The comment has been removed
• Anonymous
  August 24, 2015
  The comment has been removed