Tag: SSIS

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

SQL Server 2008 R2 Tools

SQL Server 2008 R2 comprises number of new features and tools that can be used to develop and manage databases and database solutions.

SQL Server Management Studio

SQL Server Management Studio SSMS first launched with Microsoft SQL Server 2005. It’s used as an integrated environment for accessing, configuring, managing, and administering all components within SQL Server. Management Studio includes number of graphical tools and rich script editors.

Business Intelligence Development Studio

Business Intelligence Development Studio BIDS is an integrated environment for developing business intelligence solutions including Analysis Services cubes, data sources, Reporting Services reports, and Integration Services packages.

It is based on the Microsoft Visual Studio development environment with  customised SQL Server solutions and projects. One solution can include projects of different type and independent from specific server.

Reporting Services Components and Tools

SQL Server 2008 Reporting Services SSRS makes available Report server components for data and report processing and report delivery. It also include Report Designer, Report Builder, Model designer, Report manager and Reporting Services Configuration tool  to configure a Reporting Services installation.

Integration Services Tools and Utilities

SQL Server 2008 Integration Services SSIS provides complete SSIS package creation and management tools such as SSIS Designer for building data transformation packages that include control flows, data flows, and event-driven logic, Query Builder, Expression Builder, and several command prompt utilities for managing and running packages.

SQL Server Configuration Manager

SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers.

SQL Server Profiler

SQL Server Profiler is a tool that captures SQL Server events from an instance of the Database Engine. The events can be saved in a trace file or in database for analysis and troubleshooting.

Database Engine Tuning Advisor

SQL Server Database Engine Tuning Advisor assists database administrator in creating effective indexes, indexed views, and partitions.

Command Prompt Utilities

The SQL Server Database Engine offers additional tools that can be run from the command prompt. Most popular tools are bcp, sqlcmd and tablediff. Bcp (bulk copy program) tool addresses the bulk movement of data into and out of a SQL Server database. Sqlcmd enables database administrator interacting with SQL Server from the command line. Tablediff command line utitlity provides the ability to compare the content of two tables in a database.