Get Parameter values used when package ran using SMO

Iain Barnetson 106 Reputation points
2020-12-20T13:59:43.587+00:00

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.
1,948 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.
4,732 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Iain Barnetson 106 Reputation points
    2020-12-20T16:29:36.88+00:00

    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]
    $ssisProject.Parameters 
    

    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
     TL.Exports']/ParameterInfo[@Name='NetworkInternalCode']
    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']
    
  2. Monalv-MSFT 5,691 Reputation points
    2020-12-21T07:52:19.507+00:00

    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,
    Mona

    ----------

    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,691 Reputation points
    2020-12-28T02:25:48.247+00:00

    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,
    Mona


  4. Yitzhak Khabinsky 20,551 Reputation points
    2020-12-28T15:15:56.487+00:00

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

    USE SSISDB  
    GO  
      
    -- 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:
    ssis-execution-parameter-values
    ssis-catalog-execution-parameter-values