CODEWITHSUNDEEP
ssis
Rob Bowman
Rob Bowman

Reputation: 8721

SSIS SQL Task - "Parameter name is unrecognized"

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.

Upvotes: 45

Views: 85345

Answers (7)

Divy Mistry
Divy Mistry

Reputation: 21

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 "?"

Upvotes: 0

Eugene P.
Eugene P.

Reputation: 1

When defining the parameter mappings any trailing blanks after the parameter name can cause this message too.

Upvotes: 0

Heema
Heema

Reputation: 51

If you are using more than 1 parameter then in the execute sql task window go to parameter mapping and set the parameter name to 0,1,2,3....depending on the number of parameter and the parameter size to -1.. This must be helpful to resolve your issue.

Upvotes: 5

john.da.costa
john.da.costa

Reputation: 4835

The answer to this is to change the Parameter Name value in the Parameter Mapping screen.

Given the following query 

SELECT Id, AnimalName FROM dbo.Farm WHERE Farm_id = ?

Assuming my Parameter is an integer Variable named User::Farm_id
Choose the following values on the Parameter Mapping Screen

Variable Name  -   User::Farm_id
Direction      -   Input
Data Type      -   LONG
Parameter Name -   0
Parameter Size -   -1

Originally the Parameter Name will be "NewParameterName". Simply change this to the ordinal position of your variable marker ("?")

Upvotes: 115

William Salzman
William Salzman

Reputation: 6446

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.

Upvotes: 3

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

See SSIS data types.

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

Upvotes: 1

cyberconte
cyberconte

Reputation: 2398

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

Upvotes: 0

Related Questions