SSIS SQL Task - "Parameter name is unrecognized"

Question

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.

Answer 1

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

Answer 2

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.