Data flow issue

2
votes

Getting error when trying to run Sql Command in SSIS package.

  • Task: DataFlow Task
  • Connection: ADO.NET
  • Data Access mode: Sql Command

  • Sql text:

    select from table where field1 = ? and field2 = ?

Error:" No value given for one or more required parameters"

More Information:

Execute Sql task in package:

(General tab) - Connection: ADO.NET - SQL Statement: exec storedprocedureX ?,?

(Parameter Mapping tab)

User::field1 , Input , String , 0 , -1
User::field2, Input, String, 1, -1

Variables set in package

  • field1 value 12C
  • field2 value 15A

What am I missing that is causing the variable values to not be read at Data flow level? I have no problem at the Execute SQL task level.

1
sql-serverssisdataflow
SqlClient uses names rather than ? as parameter markers. Change the query to something like select from table where field1 = @field1 and field2 = @field2; and define the parameters with those names. – Dan Guzman

1 Answers

2
votes

An OLE DB Command in the data flow is different compared to the Execute SQL Task in the control flow. You seem to be describing the Execute SQL Task correctly.

To use a variable in the data flow, you need to add it to the data flow -- the easiest way is to use a Derived Column with an expression. Add a Derived Column to your Data Flow before the OLE DB Command and configure it as follows: Derived Column Name: field1; Derived Column: add as new column; Expression: @[User::field1]. Then in the OLE DB Command, under Column Mappings map the columns as Input Column: field1; Destination Column: Param_0, etc.