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

Answer 1

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.