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).


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:


$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

# 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)

$folder = $catalog.Folders[$FolderName]

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

# 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)

$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)

# 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 ..." 
        [System.TypeCode]::String, "MSFT", $false, "Customer ID")
    $customerID = $environment.Variables["CustomerID"];

$package = $project.Packages["Package.dtsx"]


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!


Comments (4)

  1. Mikes4u says:

    Nice Article. Matt Masson's script was not as complete and left me looking for something closer to production ready!

  2. Tee says:

    In this line: $catalog = $integrationServices.Catalogs[$SSICatalog]

    I think your $SSICatalog variable is meant to be $SSISCatalog ?

  3. Michael Buller says:


    Could this be expanded to detect version of Sql installed and deploy the proper 2012 vs 2014.  Also do you think it would be possible to detect if required third party components that are referenced in the packages are installed on the ETL server that these packages are being deployed and resolve / register / GAC the components there?  

    Nice post and thank you.

  4. Chase says:

    I was having issues with this line

       $catalog = $integrationServices.Catalogs[$SSICatalog]

    Changing it to this made it a little less brittle (returns $null instead of throws exception)

       $catalog = $integrationServices.Catalogs | ? { $_.Name -eq $SSISCatalog }

Skip to main content