Category: SSIS

SQL Server Integration Services

SSIS “Multiple-step OLE DB operation generated errors”

Product: SQL Server Integration Services SSIS

Package description: Processing text files by importing them into database. The final step in the process was to execute stored procedure to set various flags in the database for all file names processed.

 

SSIS control flow extract

 

I used “for each loop container” with ADO enumerator and Execute SQL task with OLE DB type connection.

For each loop container with ADO enumerator

 

SQL statement – direct input, stored procedure with 1 parameter where parameter mapped to the proper variable name & data type.

Problem:

Error: [Execute SQL Task] Error: Executing the query “EXEC dbo.StoredProcedire ?” 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.

 

Possible Solution

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

My confusion came that the parameter I used in SQL Task was assigned in For each loop container and it had index of 1.

So the set up should be like below to avoid any errors

 

Execute SQL Task parameter mapping

For Each Loop Variable Mappings

 

Here is the link from Microsoft technet site: http://technet.microsoft.com/en-us/library/ms140355.aspx

The parameter markers that you use in parameterized SQL statements depend on the connection type that the Execute SQL task uses.

Connection type Parameter marker
ADO ?
ADO.NET and SQLMOBILE @<parameter name>
ODBC ?
EXCEL and OLE DB ?

Parameter names that you use depend on the connection type that the Execute SQL task uses.

Connection type Parameter name
ADO Param1, Param2, …
ADO.NET and SQLMOBILE @<parameter name>
ODBC 1, 2, 3, …
EXCEL and OLE DB 0, 1, 2, 3, …

 

Other reasons this particular error could come up:

  1. Parameter data type is set incorrectly comparing with the datatypes of the parameters in the store procedure in the database
  2. Number of ? passed in the store procedure in Execute SQL Task don’t match with the number of parameters in the store procedure in the database
  3. Number of query parameters don’t match the number of variables mapped
  4. Order of parameters don’t match order of the variables