Get Parameter values used when package ran using SMO

Iain Barnetson 106 Reputation points

I get details of SSIS packages that failed when ran in the last 24hrs using the code below.

$sqlConnectionString = "Data Source=$SsisServer;Initial Catalog=master;Integrated Security=SSPI"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$ssisServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection
$ssisCatalog = $ssisServer.Catalogs["SSISDB"]
$ssisCatalog.Executions | ?{ $_.StartTime -gt (Get-Date).AddHours(-24) -And $_.Status -eq 'Failed' } 

In the results returned, I extract $.ProjectName, $.PackageName, $.ExecutedAsName, $.CallerName, $_.Messages.
But I can't find how to get the parameter values passed to each execution of a package.
When I query an execution, I can't find the parameter values.

$ssisCatalog.Executions | ?{ $_.Id -eq 252836}

How can I get the values passed to parameters when the package is executed?

  • Using SMO so that I can the information in one process, and not have to use a separate process.
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,466 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,404 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Iain Barnetson 106 Reputation points

    When I look using SSMS, I can see the SSIS dtsx package being called for each of our customers, using a parameter called NetworkInternalCode. It appears that that value is passed to the package by the Project calling it.

    $ssisProject = $Ssiscatalog.Folders[$FolderName].Projects[$ProjectName]

    Shows this, see below, and it's the value of that NetworkInternalCode that is passed to '$PackageName = 'MasterETL.dtsx'' that I want to find for each time it is run.

    Name   : NetworkInternalCode
    Id : 135794
    ProjectId  : 11
    ObjectType : 20
    ObjectName : mycompany.ETL.Exports
    DataType   : String
    Required   : False
    Sensitive  : False
    Description: MAKE SURE to leave as EMPTY string BEFORE merge
    DesignDefaultValue : 
    DefaultValue   : 
    ValueType  : Literal
    ValueSet   : False
    ReferencedVariableName : 
    ValidationStatus   : N
    IdentityKey: ParameterInfo[@Name='NetworkInternalCode']
    Urn: IntegrationServices[@Name='ETLSRV\PROD']/Catalog[@Name='SSISDB']/CatalogFolder[@Name='mycompany_Exports']/ProjectInfo[@Name='mycompany.E
    Properties : {Name=Name/Type=System.String/Writable=False/Value=NetworkInternalCode, Name=Id/Type=System.Int64/Writable=False/Value=135794, 
     Name=ProjectId/Type=System.Int64/Writable=False/Value=11, Name=ObjectType/Type=System.Int16/Writable=False/Value=20...}
    Metadata   : Microsoft.SqlServer.Management.Sdk.Sfc.Metadata.SfcMetadataDiscovery
    Parent : ProjectInfo[@Name='mycompany.ETL.Exports']
    0 comments No comments

  2. Monalv-MSFT 5,896 Reputation points

    Hi anonymous user ,

    May I know if you find the solution to your question?

    1. If yes, please click "Accept Answer" on your own answer.
    2. If not, please refer to the following links :
      Run an SSIS package with PowerShell
      Execute SSIS Package from PowerShell - Part 1

    Best Regards,


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?

  3. Monalv-MSFT 5,896 Reputation points

    Hi anonymous user ,

    We can also use the following methods to execute the ssis package with parameters:

    1.Run an SSIS package from the command prompt with DTExec.exe

    Dtexec /isserver "SSISDB\MyFolder\MyProject\MyPackage.dtsx" /server "." /parameter $Project::myparam;myvalue /parameter anotherparam(int32);12

    Please refer to dtexec Utility.

    2.Run an SSIS package with C# code in a .NET app

    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 3 });

    long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

    Please refer to Execute SSIS 2012 Package with Parameters via .Net.

    Best Regards,

  4. Yitzhak Khabinsky 25,201 Reputation points

    If you are using SSISDB Catalog it is very easy. You just need to know execution_id.
    Here is the script.

    -- Filter data by execution id   
    DECLARE @executionIdFilter BIGINT = 54;  
    SELECT * FROM [catalog].[execution_parameter_values] WHERE [execution_id] = @executionIdFilter and [value_set] = 1;  
    SELECT * FROM [catalog].[execution_property_override_values] WHERE [execution_id] = @executionIdFilter;  

    Useful links:

    0 comments No comments