SSIS Execute SQL Task SQL Command Issue

Question

I have the following insert statement in my execute sql task in SSIS:

INSERT INTO dbo.SSISLogTest 
   (NodeID, BusinessDate, StartDate, StopDate, StepName, RecordCount, Message, Status, UserID)
VALUES        (?,?,?,?,?,?,?,?,?)

When I run it within the task providing parameters it executes fine and inserts a record in the table.

When I run the package, this step fails with the following error:

"[Execute SQL Task] Error: Executing the query "INSERT INTO dbo.SSISLogTest ..." 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. "

I'd say you haven't set up your parameters correctly in SSIS. Can you detail how you've done it? Passing parameters via variables into packages is fiddly in SSIS and can be prone to errors if not done in specific ways. — mal-wan

Kristian Wedberg Kristian Wedberg · Accepted Answer · 2011-11-09T12:24:29

Different connection providers require different syntax - all of the following must be set correctly:

The connection type (i.e. OLE DB, ADO...) Your choice, but aim to use the same throughout your application.
The number and specifics (Variable Name, Direction, Data Type, Parameter Name, Parameter Size) of parameters on the "Parameter Mapping" dialog.
The parameter syntax in the SQL query (i.e. your question marks.)

Please see an OLEDB example in the screenshots below and refer to Working with Parameters and Return Codes in the Execute SQL Task for details.

enter image description here

Execute SQL Task - General

enter image description here

SSIS Execute SQL Task SQL Command Issue

1 Answers