SSIS and PowerShell in SQL Server 2012

SSIS and PowerShell in SQL Server 2012

Rate This
  • Comments 16

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            
            
Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post
  • I like this integration of SSIS with Shell program.

    can we use shell program to add datatap?

  • The way to think about powershell API is anything that you can do with MOM can be done with powershell. Since you can add a data tap using MOM, you can do the same using powershell

  • I think the example in scenario #1 is not going to run because the $package variable was not initialized and $executionId is not set in the 2nd.

  • Where is Microsoft.SqlServer.Management.IntegrationServices located?   I've searched everywhere in a full installation of 2012 RTM and have not found that assembly

  • You should be able to find it in the SSMS directory - C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio

  • It is not there. The DLL appears in GAC though, but not as a file anywhere. There are a few things to keep in mind: the target in your VS may not be .Net 4.0, and the reference you add directly to the project file, then it appears possible to use the IntergationServices class and the rest (as Catalog)

    There was a discussion here: http://bit.ly/JTe3Ap that may be beneficial.

    Thing is I get warnings switching to 3.5 as my target regarding the Microsoft.SQLServer.ManagedDTS reference in my VS 2010

  • Yup - I'm the same Chuck from that thread ;)  Interesting that the dll appears nowhere but the GAC

  • Ahh, sorry guys - it looks like we changed this in the RTM build. You're correct that it is only found under the 2.0/3.5 GAC (C:\windows\assembly)

  • Hi Matt,

    How can we delete any folder inside catalog using powershell?

  • $ssis.Catalogs["SSISDB"].Folders["Folder"].Drop().

    msdn.microsoft.com/.../microsoft.sqlserver.management.integrationservices.catalogfolder.aspx

  • Thanks for the script and have got it working for my project. :-)

    One typo though in your example; when adding variables into environment, for sensitivity, instead of "false" it should be $false

    Cheers!

  • How is this related and compared to using Microsoft.SqlServer.Dts.Runtime namespace for executing SSIS packages? Is this for SQL 2012 only? What other fundamental differencies are they? Thanks!

  • You refer to variable $SSISEnvVar but this is not set anywhere?

  • My script runs fine upon the initial setup, but fails when I run script the second time. is there a way to execute the script so that if the project already exists and you deploy it again, it overrides the project, variables, ect and doesn't error out. when the deploy the project  the second time, i always get the error, "cannot index into null array"

  • How do you remove an environment reference?

Page 1 of 2 (16 items) 12