SSIS and PowerShell in SQL Server 2012

First published on MSDN on Nov 17, 2011

This post is from Parth Shah, a Software Development Engineer in Test on the SSIS Team.

Previously we have talked about SSIS Catalog Managed Object Model . For those of you don’t remember what MOM is or have not heard of it before, think about MOM as a set of APIs that allow you to automate configuring, deploying, validating and executing your projects and packages in a seamless way. We showed an example of how to achieve this through C# but did you know you can achieve the same result through Windows PowerShell?

This post focuses on the usage of the SSIS Catalog Managed Object Model through Windows PowerShell. Over the course of this blog entry, we will go over a couple scenarios, so you can see how to achieve different tasks using PowerShell. For each scenario, we will go over a goal of what we are trying to achieve and then a simple plan of how we are going to achieve it. And finally, we will present the code that does the actual job.

Scenario #1 – Setting up Integration Services Server to execute a package

Plan:

1. Establish a connection to the server.
2. Retrieve the Integration Services object.
3. Create all objects needed to deploy our project.
4. Deploy project and then execute our package.

# Load the IntegrationServices Assembly
$loadStatus = [Reflection.Assembly]::Load("Microsoft"+
".SqlServer.Management.IntegrationServices" +
", Version=11.0.0.0, Culture=neutral" +
", PublicKeyToken=89845dcd8080cc91")

# 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
$constr = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"

$con = New-Object System.Data.SqlClient.SqlConnection $constr

# Create the Integration Services object
$ssis = New-Object $ISNamespace".IntegrationServices" $con

## Drop the existing catalog if it exists
# Write-Host "Removing previous catalog ..."
# if ($ssis.Catalogs.Count -gt 0)
# {
# $ssis.Catalogs["SSISDB"].Drop()
# }

# Provision a new SSIS Catalog
Write-Host "Creating new SSISDB Catalog ..."
$cat = New-Object $ISNamespace".Catalog" ($ssis, "SSISDB", "#PASSWORD1")
$cat.Create()

# Create a new folder
Write-Host "Creating Folder ..."
$folder = New-Object $ISNamespace".CatalogFolder" ($cat, "Folder", "Description")
$folder.Create()

# Read the project file, and deploy it to the folder
Write-Host "Deploying ExecutionDemo project ..."
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes("C:\Demos\Demo.ispac")
$folder.DeployProject("ExecutionDemo", $projectFile)

# Run the package
Write-Host "Running package ..."

# When executing, we need to specify two parameters
# 1 arg is a bool representing whether we want to run
# 32bit runtime on 64 bit server
# 2 arg is a reference to an environment if this package depends on it
$executionId = $package.Execute("false", $null)

Write-Host "Package Execution ID: " $executionId

Scenario #2 – Executing complex packages with parameters

Goal: To run a complex package that has parameters that need to filled in

Plan:

1. Repeat everything from previous scenario up to deploying project



2. Fill in the values for parameters by either specifying constants or creating environments

# Load the IntegrationServices Assembly
$loadStatus = [Reflection.Assembly]::Load("Microsoft"+
".SqlServer.Management.IntegrationServices" +
", Version=11.0.0.0, Culture=neutral" +
", PublicKeyToken=89845dcd8080cc91")

# 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
$constr = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"

$con = New-Object System.Data.SqlClient.SqlConnection $constr

# Create the Integration Services object
$ssis = New-Object $ISNamespace".IntegrationServices" $con

## Drop the existing catalog if it exists
# Write-Host "Removing previous catalog ..."
# if ($ssis.Catalogs.Count -gt 0)
# {
# $ssis.Catalogs["SSISDB"].Drop()
# }

# Provision a new SSIS Catalog
Write-Host "Creating new SSISDB Catalog ..."
$cat = New-Object $ISNamespace".Catalog" ($ssis, "SSISDB", "#PASSWORD1")
$cat.Create()

# Create a new folder
Write-Host "Creating Folder ..."
$folder = New-Object $ISNamespace".CatalogFolder" ($cat, "Folder", "Description")
$folder.Create()

# Read the project file, and deploy it to the folder
Write-Host "Deploying ExecutionDemo project ..."
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes("C:\Demos\Demo.ispac")
$folder.DeployProject("ExecutionDemo", $projectFile)

#### NEW STUFF STARTS FROM HERE ####

# we can specify the value of parameters to be either constants or
# to take the value from environment variables

$package = $project.Packages[“ComplexPackage.dtsx”]

# setting value of parameter to constant
$package.Parameters["Servername"].Set(
[Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,
"Foobar");
$package.Alter()

# binding value of parameter to value of an env variable is a little more complex
# 1) create environment
# 2) add variable to environment
# 3) make project refer to this environment
# 4) make package parameter refer to this environment variable
# These steps are shown below

# 1) creating an environment
$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, “Env1”, “Env1 Desc.”)
$environment.Create()

# 2) adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
$environment.Variables.Add(“Variable1”, [System.TypeCode]::Int32, “10”, “false”, “Desc.”)
$environment.Alter()

# 3) making project refer to this environment
$project = $folder.Projects[$SSISProjectName]
$project.References.Add($SSISEnv, $folder.Name)
$project.Alter()

# 4) making package parameter refer to this environment variable
$package.Parameters["CoolParam"].Set(
[Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,
$SSISEnvVar)
$package.Alter()

# retrieving environment reference
$environmentReference = $project.References.Item($SSISEnv, $folder.Name)
$environmentReference.Refresh()

# executing with environment reference – Note: if you don’t have any env reference,
# then you specify null as the second argument
$package.Execute("false", $environmentReference)

Write-Host "Package Execution ID: " $executionId