Problem
We have a couple of workflows scripted out in PowerShell and in one of those workflows, we would like to execute an SSIS package that was created by another team. Is this possible? And if so, can we pass parameters to the package?
Solution
PowerShell is a powerful cross-platform scripting language, which is popular by admins to automate tasks and workflows. In this part of the tip, we’ll take a look on how we can use a PowerShell script to execute an Integration Services (SSIS) package, stored in the SSIS catalog on a local machine (thus using the project deployment model).
Execute SSIS from PowerShell
Let’s create a simple package (using the project deployment model) which we will then execute from PowerShell. The package will have one Execute SQL Task, which will write a message to a table in a database. Through a package parameter, we can change this message. In an SSIS project, create a new package and add an Execute SQL Task to the canvas. Also create a connection to your SQL Server database of choice. In this database, create the following table:
CREATE TABLE dbo.PowerShellTest (ID INT IDENTITY(1,1) NOT NULL, TestMessage VARCHAR(100) NOT NULL);
Then create a package parameter of the string data type:
As default message, we use “Hello World!”. Next, create a variable, again of the string data type. This variable will contain the T-SQL statement that the Execute SQL Task will execute. Using an expression, we will insert the value of the parameter into the SQL statement.
The expression is as follows:
"INSERT INTO dbo.[PowerShellTest]([TestMessage])
VALUES ('"+ @[$Package::Parameter] + "');"
Configure the Execute SQL Task to use this variable as input for the SQL statement:
When we debug the package, we can see the result in the table:
Execute Package by Connecting to the SSIS Catalog
Before we do anything, we need to make sure we have the SQL Server cmdlets installed on your machine, otherwise you might get this error:
With the following command we can install this module:
Install-Module SqlServer
If you’re asked to install from an untrusted repository (PowerShell Gallery), answer with Yes (Y) or Yes to All (A).
Let’s take a look at how we can execute an SSIS package with a simple command. First, we need to invoke SQLCMD:
Invoke-Sqlcmd | Out-Null
Then we set the location to SQL Server:
Set-Location SQLSERVER:\SQL
Then we can retrieve the list of packages using Get-ChildItem. This list will be filtered using the package name and the resulting package will be executed using the Execute method. The entire PS command:
(Get-ChildItem SQLSERVER:\SSIS\localhost\Default\Catalogs\SSISDB\Folders\MSSQLTIPS\Projects\MSSQLTIPS\Packages\ | WHERE { $_.Name -eq 'ExecuteFromPowerShell.dtsx' }).Execute("false", $null)
For a visual reference, here’s how the package is stored inside the catalog:
The Execute method accepts at least two parameters:
- The first one is use32RuntimeOn64, which determines if the 32-bit runtime should be used or not.
- The second one is reference, which is a reference to an environment created in the catalog. If you don’t want to specify an environment, just pass NULL.
The optional third and fourth parameters allow you to specify values for the package parameters or for property overrides respectively.
The Execute method will return a number, which is the execution ID of the package. This means it doesn’t return if the package failed or not! In the following screenshot, the package is executed twice. The first time it failed (because of a connection time-out, which can be common with an Azure SQL Database in the serverless compute tier), the second time it succeeded.
When looking at the All Executions report in the SSIS catalog for the package, we can see both execution IDs:
In the table, we can see a new row has been added:
The command is useful for an ad-hoc execution of a single package, but in more realistic use cases more control is probably needed. Let’s expand into a full PowerShell script. First, we’re going to use some variables to store all the info about the folder, the project and the package. We’re also storing the name of the SSIS namespace.
# Variables $SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" $TargetServerName = "localhost" $TargetFolderName = "MSSQLTIPS" $ProjectName = "MSSQLTIPS" $PackageName = "ExecuteFromPowerShell.dtsx"
Next, we’re loading the SSIS assembly, creating a connection to the SQL Server database where the catalog is stored and we’re creating a new SSIS object.
# Load Integration Services assembly $loadStatus = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Management.IntegrationServices") | Out-Null$sqlConnectionString = ` "Data Source=" + $TargetServerName + ";Initial Catalog=master;Integrated Security=SSPI;" $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString # Create SSIS object $integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection
Now we can load the catalog, the folder, the project and the desired package into PowerShell.
# Get SSIS catalog $catalog = $integrationServices.Catalogs["SSISDB"] # Get folder $folder = $catalog.Folders[$TargetFolderName] # Get project $project = $folder.Projects[$ProjectName] # Get package $package = $project.Packages[$PackageName]
Finally, we can execute the package using the same execute method we used before:
$result = $package.Execute("false", $null)
Again, the $result variable will hold the executionID of the package and is not an indicator for success. Using the PowerShell ISE, we can execute the script:
Passing Parameters to the Package
Let’s find out how we can alter the behavior of a package by passing input parameters. To do this, we’re going to set the parameter value using the Set method of the ParameterInfo class. After setting the parameter, the package is altered.
The last piece of the script now becomes:
# Get package $package = $project.Packages[$PackageName] $paramname = "Parameter" $paramvalue = "Hello MSSQLTips!" $package.Parameters[$paramname].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$paramvalue$package.Alter() Write-Host "Running " $PackageName " with param..." $result = $package.Execute("false", $null) Write-Host "Done. " $result
When the script is executed, you can see the parameter value has changed:
Because we used the Alter method of the package, the design time value of the parameter is overwritten in the SSIS catalog:
This change is permanent on the server. This is OK if you want to use the parameter value every time the package is run inside the catalog, but not if it was intended for one execution only. If we want to set the parameter value for a single execution, the script becomes a bit more complicated.
We have to use the ExecutionValueParameterSet class. This object will contain the name, the value and the object type. The object type determines what kind of property we want to set. The documentation of the .NET object model is not very clear, but luckily the documentation of the T-SQL counterpart is. The value 20 is for project parameters, the value 30 for package parameters. For setting certain system parameters, such as SYNCHRONIZED, you have to use the value 50.
For each parameter we want to change, we need to create an ExecutionValueParameterSet object and put those into a collection. When we use the Execute method, we will pass this collection as input. The script becomes:
# Get package $package = $project.Packages[$PackageName] $paramname = "Parameter" $paramvalue = "Hello MSSQLTips Ad-hoc!" $setValueParameters = New-Object 'System.Collections.ObjectModel.Collection[Microsoft.SqlServer.Management.IntegrationServices.PackageInfo+ExecutionValueParameterSet]'$executionParameter = New-Object 'Microsoft.SqlServer.Management.IntegrationServices.PackageInfo+ExecutionValueParameterSet'$executionParameter.ObjectType = 30$executionParameter.ParameterName = $paramname$executionParameter.ParameterValue = $paramvalue$setValueParameters.Add($executionParameterWrite-Host "Running " $PackageName " with param..." $result = $package.Execute("false", $null, $setValueParameters) #overloaded Execute Write-Host "Done. " $result
When the script is executed, we can verify the parameter value has changed:
At the same time, the server value of the parameter is still “Hello MSSQLTips!”.
Output Parameters
SSIS packages don’t have a mechanism for output parameters. If you want your SSIS package to return something, an option is to write it to a table or a file, like we did in the sample package. In PowerShell, you can connect to SQL Server and read data from a table. An example is given in the tip Getting the Count of Non-Null Values in a SQL Server Table with PowerShell.
Package Success or Failure
As demonstrated before, PowerShell only returns the execution ID of the package execution. The Execute method doesn’t return if the package succeeded or not. However, with the execution ID we can find the result in the SSISDB catalog view catalog.executions.
Status code 4 means failure, while status code 7 means success. You can find the other possible codes in the documentation.
Next Steps
- Try it out yourself! You can find two scripts in this zip file. The first script is where we set the server value of the parameter, the second script uses an execution value for the parameter.
- As an exercise for the reader, you can try to write a few lines of PowerShell that will fetch the execution result from the catalog view.
- You can find more PowerShell tips in this overview.
- Getting started with SSIS? Check out this tutorial.
