Overview: Quick tip about resolving a error found in SQL Server Integration Services when mapping query parameters and variables.

Ever find where you so commonly make the same small mistake that you immediately recognize the error when you see it? Yup, I have one of those too. Let me walk you through it: In SSIS, I set up an Execute SQL Task. Within the SQL statement I use a ? to indicate a query parameter. Then I go to the Parameter Mapping page and map the parameter to the proper Variable Name & set the Data Type. Then I run the task to test it. Uh oh, here comes the following error:

[Execute SQL Task] Error: Executing the query "UPDATE Stage_Source.MasterAcctHist SET YTD01 = ..." 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.

The thing I’ve found myself missing a couple of times recently is changing the Parameter Name. Left unchanged, it defaults to “NewParameterName.” In order for SSIS to properly map the query parameter to the variable, it needs the correct naming convention. In my case, I’m using an OLEDB connection type so the first Parameter Name in the list it’s looking for is a 0 (followed by 1,2,3). When it’s set up, it looks like this:

The actual Parameter Name SSIS is looking for depends upon your connection type – the MSDN link below is a handy reference.

Other reasons this particular error could come up is if your Data Type is set incorrectly, or if the number of query parameters don’t match the variables mapped (and in the correct order).

Finding More Information

MSDN – Map Query Parameters to Variables in Execute SQL Task