SSIS SQL Task - "Parameter name is unrecognized"

ssis
Rob Bowman picture Rob Bowman · Jan 5, 2010 · Viewed 69.3k times · Source

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

johndacostaa picture johndacostaa · Aug 27, 2010

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

Related questions

How to pass variable as a parameter in Execute SQL Task SSIS?

I have ssis package in that I'm taking values from flat file and insert it into table. I have taken one Execute SQL Task in that creating one temptable CREATE TABLE [tempdb].dbo.##temptable ( date datetime, companyname nvarchar(50), price decimal(10,0), …

Read More
SSIS Convert Between Unicode and Non-Unicode Error

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table. All columns except a date column are NVARCHAR(255). I am using an Excel destination and using a SQL statement to create the sheet …

Read More
What is the SSIS package and what does it do?

Can someone tell me what the SSIS package is all about? I am totally new to data-warehousing and I am going crazy with the terms SSIS, DTL, ETL and so on. I am looking for a big picture and how …

Read More