SSIS SQL Task - "Parameter name is unrecognized"

Question

Hi

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

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

Answer 2

See SSIS data types.

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

Answer 3

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.

Answer 4

I hate freaking SSIS

Answer 5

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