SSIS 2012 Passing Parameter to a Package using CATALOG.start_execution

4
votes

I'm working on a workflow to read in details from a config table and pass the details to a package. The package in question is loading a CSV into the DB based on the filename passed as parameter (which is being read from a static DB table).

I have the bones working, I just can't understand how to correctly configure the parameter in the target package to use it correctly.

In my calling procedure I set the parameter and execute the package as 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

The object_type seems to have 3 options for project, package or execution parameters...

Within my target package, I have Parameter1 defined as a package parameter (listed in the parameter tab from the package design

Ive got a "Flat File Source" configured and the connection manager properties->Expressions, I have the ConnectionString override set to @[Package::Parameter1] which doesn't seem to work.

Thanks.

.. update. From the execution_parameter_values view, I have highlighted what is passed through 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;Initial Catalog=StagingDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

1
parametersssispackage
what is the value of @Parameter1 that you are passing in to the package during execution? - Bill Anton
Hi banton - updated with values from execution_parameter_view - WelshJim
Is the SQL Server instance where the package is stored and being run from the same system where the csv file (C:\abc.csv) is located? Also, are you getting error when you try executing? If so, what's the error? - Bill Anton

1 Answers

0
votes

Referencing Package level variables will use the @[Namespace::VariableName] pattern you have shown.

Parameters, be they Package or Project will use the syntax of @[$Project::VariableName] and/or @[$Package::VariableName] .

Thus, in your expression on the ConnectionString, you should have used @[$Package::Parameter1] (and chosen a better name but you already knew that).