October 22, 2007

Execute SQL Task (SSIS) with parameters

On Integration Services 2005, when using a query with parameters in the Execute SQL Task, mind that in the Parameter Name field the counting starts at 0 (if, at least, the connection type is OLE DB). Therefore, if you are using multiple parameters the first Parameter Name must be 0, the second must be 1, and so on.
Otherwise, you'll get this error:
[Execute SQL Task] Error: Executing the query "DELETE FROM myTable WHERE key = ?" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Check here for more information: http://msdn2.microsoft.com/en-us/library/ms187685.aspx

No comments: