SSIS SQL Task - "Parameter name is unrecognized"

user_41169 0
Viewed 77348

I have a SQL Task that needs to run a simple update to update a single row.

I have set the SQLStatement to: 

update agency set AgencyLastBatchSeqNo = ? where agencyID = ?

On the Parameter Mapping page I gave set Parameter 0 and Parameter 1 to variables that I know contain the right values. I have also set the Parameter Name values correctly.

In the database, the column AgencyLastBatchSeqNo is an int, AgencyID is a big int. Does anyone have a reference to find what the data types map to in SSIS? I have guessed at SHORT for the int and LONG for the big int.

When I run the task I get the following error:

[Execute SQL Task] Error: Executing the query "update agency set AgencyLastBatchSeqNo = ? where AgencyID = ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Could anyone please suggest what may be wrong?

Thanks

Rob.

ssis
7 Answers

One thing you don't mention is your connection type. I assume you are not using ADO.Net since the parameter marking in that case is not a ?. For the other types of connection, parameters are named as follows:
ADO (not ADO.Net) connection: parameter names are Param1, Param2...
ODBC connection: parameter names are 1,2,3... OLEDB connection: parameter names are 0,1,2...

For the variable types (they are different in the parameter mapping section than in any other area of SSIS) I typically use Long for Int's and I typically leave the length set to -1. I believe that a Long will work for both Int's and Bigint's.

user_2363480

See SSIS data types.

   int = DT_I4  (4 byte integer) = Int32 variable
bigint = DT_I8  (8 byte integer)  = Int64 variable
user_1967130

Make sure you're quoting your values, and that you don't have typos in your column names.

user_963900

Go to Parameter Mapping and change Mapped Parameter_Name = 0 instead of using default name. and for next parameter it should be 1. and so on...

Note:- you have to change the "Data Type" as per the value.

it will just take place on the occurrence of "?"

user_149897630
Related