SSIS 2012 Passing a parameter to a package using CATALOG.start_execution

Question

I am working on a workflow to read the details from the configuration table and transfer the details to the package. This package loads the CSV into the database based on the name of the file passed as a parameter (which is read from the static database table).

I have bones, I just can not figure out how to properly configure the parameter in the target package in order to use it correctly.

In my calling procedure, I set the parameter and executed the package as shown below

.... EXEC CATALOG.set_execution_parameter_value @ExecutionID ,@object_type = 30 -- (30 is package param!) ,@parameter_name = N'Parameter1' ,@parameter_value = @Parameter1 EXEC CATALOG.start_execution @ExecutionID 

In the object_type object there are 3 options for the parameters of the project, package or execution ...

In my target package, I have Parameter1 defined as a package parameter (specified on the options tab from the package design

I have "Flat source file" and connection manager properties → Expressions installed, I have a ConnectionString override set to @ [Package :: Parameter1], which does not seem to work.

Thanks.

.. update. From the execute_parameter_values ​​view, I highlighted what is being transmitted to the package: -

param_idexe_id | object_t | param_t | PARAMETER NAME | parameter_value
3492 | 10184 | 20 | String | Parameter1 |
3493 | 10184 | 30 | String | Parameter1 | C: \ abc.csv
3494 | 10184 | 30 | String | CM.BLACKBEAST-II.StagingDB.ConnectionString | Data Source = BLACKBEAST-II; Start Directory = StagingDB; Provider = SQLNCLI11.1; Integrated Security = SSPI; Automatic translation = False;

Answer 1

The link to package level variables will use the @[Namespace::VariableName] template that you specified.

The parameters, whether it be a package or a project, will use the syntax @[$Project::VariableName] and / or @[$Package::VariableName] .

So, in your expression on ConnectionString you should have used @[$Package::Parameter1] (and chose the best name, but you already knew that).