SSIS Package cannot find parameter in command line

Question

I have a DTSX file that needs to have a parameterized query. The query occurs in the "DataFlowTask" here:

Which calls the data flow task here:

Here is the parameter :

and the query:

The project builds - if I remove the parameter, it runs fine. However, I am having issues setting the parameter. I've tried probably a hundred combinations, including:

dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /set "\Package.Variables[OrderDate].Value";"1/1/2000"
dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /set "\Package.Variables[OrderDate].Value";"1/1/2000"
dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /set "\Package.Variables[User::OrderDate].Value";"1/1/2000"
dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /set "\Package.Variables[$Package::OrderDate].Value";"1/1/2000"
dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /set "\Package\DataFlowTask.Variables[$Package::OrderDate].Value";"1/1/2000"
dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /set "\Package\DataFlowTask.Variables[User::OrderDate].Value";"1/1/2000"
dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /set "\Package.Variables[OrderDate].Value;1/1/2000"

And all sorts of other combinations. There errors are a mix of "Process configuration failed to set the destination at the package path of ..." or "Package path referenced an object that cannot be found"

I'm totally lost - what am I missing?

I always use string data type for date parameters and never get issues like this. Change the data type of the variable to a string in the 'yyyy-mm-dd' format and give it a try. — TheEsnSiavashi
Well you got me started on a different set of searches and I found out that the issue is actually you can't set parameters via the command line - you have to use variables. Thanks. — James F
Yep, it is confusing when in the SQL Comand in the OLE DB source it's called Parameters but you actually need to select a Variable. — TheEsnSiavashi

Ferdipux Ferdipux · Accepted Answer · 2017-06-01T14:18:44

As your screenshot shows that OrderDate is a package Parameter, not a Variable. So, to pass it with dtexec, you need to call it this way

dtexec /f "D:\[location]\ExportOrdersWithParameter.dtsx" /Parameter 
"$Package::OrderDate(DateTime)";2008-12-22

There might be issues on how your system parses DateTime values; perhaps, you better off specifying date as YYYYMMDD or YYYY-DD-MM.

SSIS Package cannot find parameter in command line

1 Answers