Execute Parameterized SSIS Package with PowerShell

Dan Savlon 96 Reputation points
2021-07-09T12:31:14.907+00:00

Hello,

I am using the following syntax to launch an SSIS package from PowerShell:

Invoke-Sqlcmd  | Out-Null
Set-Location SQLSERVER:\SQL
(Get-ChildItem 'SQLSERVER:\SSIS\{server running SSIS}\Default\Catalogs\SSISDB\Folders\SSIS_Packages\Projects\{Project Foldername}\Packages\' | WHERE { $_.Name -eq 'Package.dtsx' }).Execute("true", $null)

While this syntax is working fine for executing a basic package, I would now like to pass a project parameter of type string from the PowerShell script to the SSIS execution. The string value will be either "test" or "prod"

How would I do that?

Also, please note that our PowerShell scripts are run on a different server than our SSIS instance.

Thank you!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,322 questions
0 comments No comments
{count} votes

1 additional answer

Sort by: Most helpful
  1. Michael Taylor 45,996 Reputation points
    2021-07-09T14:57:57.353+00:00

    The Execute method accepts an optional list of parameter values to use to override what is in the package defaults.

    The first optional parameter where you're passing $null is the environment reference. The second optional parameter (that you aren't currently passing) is a collection of parameter values. You can create an instance of the collection, add the parameters you want to set and then pass that collection to the package to override the defaults.

    I've not tried to use PS to automate IIS packages with parameters so I'm not 100% sure about the syntax but I suspect you'll have to new up the collection and add the parameters the long way. You could first try passing a hashtable of parameter name/value pairs and see if the cmdlet will auto convert it for you.

    0 comments No comments