Execute SQL Task on SSIS 2005. ADO.NET connection type and Stored Procedure Input Parameter. Not working. Syntax error

Question

I am Execute SQL Task on SSIS 2005. ADO.NET connection type and Stored Procedure Input Parameter. Not working. Syntax error I read some documentation and blogs but its not working for me. This is what I have on SQL Statement of the Exeute sql task (ADO.NET)

EXEC StoredProcedureName @ParameterName =

It gives different errors on different syntax I tried. All failture Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I tried all combination like EXEC StoredProcedureName @ParameterName = ? StoredProcedureName ? StoredProcedureName @ParameterName = ? etc etc in vain

I checked the parameter type and its fine

The same setup works if I change the connection type to OLE DB. But I don't want OLE DB and want to change it to ADO.NET

I tried as per Microsoft specification but that also didn't work per Micrtosoft If IsQueryStoredProcedure is set to False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

I tried changing all combinations on IsQueryStoredProcedure property also in vain I mapped parameter name,order and variable on Parameter mappings properly still no luck

as i said it works with OLEDB but does not work with ADO.NET even after changing syntax. Please help

I have another task on ADO.NET and it works fine because it has no input parameter

Answer 1

Try setting the IsQueryStoredProcedure to False and then setting the SqlStatement to

Execute MyStoredProcedure @MyParameter

Where @MyParameter is the Parameter Name in the Parameter Mapping section of the task, which references a variable in your package. This variable holds the value that you want to pass to the stored procedure.

When using an ADO.NET connection parameters should use the '@' notation, when using OLE DB you should use the '?' notation.

ADO.NET. Execute MyStoredProcedure @MyParameter OLE DB. Execute MyStoredProcedure ?

Remember though that when using OLE DB, you should reference the Parameter Names (in Parameter Mapping) using 0,1,2,3 etc