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.
Plan:
# 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
Goal: To run a complex package that has parameters that need to filled in
# 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
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)