we can  assign one parameter value for each excecution of  [SSISDB].[catalog].[set_object_parameter_value] by calling this catalog procedure..

Example: If I have 5 parameters in SSIS package ,to assign a value to those 5 parameters at run time should I call this   [SSISDB].[catalog].[set_object_parameter_value] procedure 5 times ? or is there a way we can pass all the 5 parameters at 1 time .

1. Wondering if there is a way to pass multiple parameters in a single execution (for instance to pass XML string values ??)

2.What are the options to pass multiple parameter values to ssis package through stored procedure.?

Thanks In Advance.

You can call set_execution_parameter_value if you want to set the parameter value at execution time.  You would call this procedure once for each parameter you want to set.

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx'
, @execution_id=@execution_id OUTPUT, @folder_name=N'TestParams'
, @project_name=N'TestProject', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = N'Test 2'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20
, @parameter_name=N'ParameterNoDefault', @parameter_value=@var0
DECLARE @var1 sql_variant = N'New Value'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20
, @parameter_name=N'ParameterWithDefault', @parameter_value=@var1
DECLARE @var2 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50
, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var2
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Note that the first two calls to set_execution_parameter_value sets the project level parameter values.  The last sets the logging level for the execution.

Seems that you posted the same question under a different login, is that possible?

My answer was yes, you need to run this code 5 times, but perhaps you can automate this process if you need to run the same package as part of a unit test with different parameter values.

 @ Arthur...

How can we automate the process ? with out calling the same procedure many times.

 perhaps you can automate this process if you need to run the same package as part of a unit test with different parameter values.

Thanks.

Hi saich55,

Not sure if you can program, if you can then the statements crafted by Russ shall become part of your test harness.

If you cannot program and have to execute the package with large number of parameters then you can compose these statements in Excel using a macro or even formulas.