Introduction to the Call Stored Procedure (OLEDB) Tool
What is the Call Stored Procedure (OLEDB) Tool?
The Call Stored Procedure (OLEDB) tool executes database stored procedures and functions to provide read, write, and automated data processing.
If a procedure generates a recordset, the task step can return this to other steps for further processing. Conversely, the task step can consume a recordset to enable multiple procedure calls or execute a single procedure. Procedure calls can be passed static or dynamic parameters to control the precise nature of the call being made.
The features provided by the Call Stored Procedure (OLEDB) tool mean that it may be used as a Data Connector, Input, Output, or Execute type step within a task.
The tool supports any RDBMS with a functional OLEDB provider, enabling native support for a multitude of data sources.
Call Stored Procedure (OLEDB) Tool Features
- Calls stored procedures from a multitude of data sources
- Allows manual or dynamic inputting of procedure parameters
- Contains memory features to memorise task data created at run-time
White Paper - Call Stored Procedure (OLEDB)
Technical Summary
Working with Other Tools
The Call Stored Procedure (OLEDB) tool can interact directly with the following tools, whether by exposing information to it or consuming information from it:
Consuming from Other Tools
The Call Stored Procedure (OLEDB) tool can consume output from the following tools:
| Icon | Tool Name | Tool Category |
|---|---|---|
 | Import Flat File | Input |
 | Convert XML to Recordset | Format |
 | Create Workflow Job | Format |
 | Format as Flat File | Format |
 | Format as HTML | Format |
 | Format as HTML Pro | Data Connectors |
 | Format as Text | Format |
 | Run Crystal Report | Format |
 | Run Microsoft Reporting Services | Format |
 | Run Microsoft Word (Merge) | Format |
 | Filter Data | General |
Objects Consumed
The following objects can be consumed by a Call Stored Procedure (OLEDB) step:
- Recordset — Information structured into columns and rows
- Variables (Text) — Textual data is extracted from selected Variables
- Documents (Text) — One or multiple plain text documents
- Documents (HTML) — One or multiple HTML documents
- Documents (Paged HTML) — One or more multiple paginated HTML documents
- Documents (PDF) — One or multiple PDF documents
- Documents (RTF) — One or multiple RTF documents
- Documents (XML) — One or multiple XML documents
- Documents (CSV) — Output from a Run Crystal Report Step
Exposing to Other Tools
The following tools can consume output from a Call Stored Procedure (OLEDB) task step. Note that the below depends whether recordsets, variables, or XML is outputted.
| Icon | Tool Name | Tool Category |
|---|---|---|
 | Convert Recordset to XML | Format |
| Convert XML to Recordset | Format |
| Create Workflow Job | Format |
 | Transfer File (FTP) | Output |
 | Run External Program | Execute |
| Filter Data | General |
Objects Exposed
The following objects, exposed by the Call Stored Procedure (OLEDB) step, can be consumed by the above tools:
- RecordSource — If an Input Recordset has been selected (see General tab), this contains the columns included in the recordset
- DocumentSource — If a Document Source has been selected (see General tab), this contains the data in the document as recordset columns
- Memory Definitions — If configured, this is a list of Memory Definitions created for the step and the recordset columns to be memorised for each one
- Step Properties — Standard step properties are available allowing you to use statistical data of the tool
Prerequisites
The Call Stored Procedure (OLEDB) tool requires the following:
- Functional OLEDB provider which can expose procedures and or functions
Adding OLEDB Connections
Before adding the Call Stored Procedure (OLEDB) tool to a task, connections to OLEDB data sources must be configured. Once configured, all connections can be used by any Call Stored Procedure (OLEDB) step.
You open this interface from the resources tree — expand System > Tools > Input, Data Connectors, Output, or Execute and double-click Call Stored Procedure (OLEDB) in the items list.
Use Properties to edit an existing connection, Remove to delete an existing connection, and Test to ensure the highlighted connection can be successfully made.
Click Add to create a new connection; select the required ODBC driver or data source.
Selecting an OLEDB Driver
Once a driver has been selected, add the connection details to the database. The configuration window is different for every driver available; the following example is shown when an SQL Server Native Client driver has been selected:
Editing OLEDB Connection Properties
After establishing the basic connection to the relevant OLEDB driver or data source, you can add more advanced configuration, if required. Highlight the relevant connection and click Properties.
General Tab Properties
- Name — The connection name
- Additional Providers Information — Where available, additional information for the connection is displayed
Connection Tab Properties
- Connection String — Contains the full connection string.
- Use Pass-Through — This property is only available with some drivers. You can choose to make the connection directly to the driver (enabled), or use the Microsoft Data Access Objects (DAO) API to make the connection to the driver.
- Reconfigure — If any changes are required, use Reconfigure to open the configuration window.
- Test — Tests the connection to the database.
- Connection Settings — If required, you can set a time-out period for connecting to the database in Seconds.
Cursors Tab Properties
Use the properties in the Cursors tab to change the type of database cursor used when executing queries.
- Basic forward only — Use this option where you expect the task to only use the results from the Call Stored Procedure (OLEDB) step once. The result set from the queried rows can only be read top-to-bottom. The cursor cannot be reset so the query cannot be re-used. All drivers support this mode.
- Advanced cursor attributes
- Re-usable (scrollable) — This property allows the cursor to move backwards and forwards through the result set, thus the Call Stored Procedure (OLEDB) output can be consumed by multiple steps in the same task.
- Do not reflect changes in underlying data source — The rows returned by a query are cached independently at the time the query is executed. When the result set is actually read, any changes made to the source data since the query was executed are not reflected in the result set.
- Reflect row deletions and changes only — Any changes made to the source data since the query was first executed (updates and deletes to rows in the result set only) are reflected.
- Reflect row deletion, changes and additions — Any changes made to the source data since the query was first executed (updates, inserts, and deletes) are reflected in the result set.
SQL Tab Properties
The properties available in the SQL tab set the supported rules for the selected OLEDB driver for the SQL statements.
For more information, refer to your OLEDB driver documentation.
Step Configuration
Even though the Call Stored Procedure (OLEDB) tool is found under three different tool categories, using one category over another does not affect the functionality of the tool.
To add a new Call Stored Procedure (OLEDB) step to an existing task, you either:
- Click and drag the Call Stored Procedure (OLEDB) icon from the Task Browser to the task Design area.
- From the task’s Design tab, right-click on empty space and select New > then either Data Connectors > Call Stored Procedure (OLEDB), Execute > Call Stored Procedure (OLEDB), Input > Call Stored Procedure (OLEDB), or Output > Call Stored Procedure (OLEDB).
For a detailed description of how to create new tasks, refer to the product help.
About the General Tab
The General tab is used to Name and describe (Description) this task step.
- Use a Recordset — Enable this parameter if recordset data from a previous task step is required when executing the stored procedure
- Input Recordset — Contains all available recordsets from steps previously created in the task
- Read documents from a source — Enable this parameter if document data from a Format task step is required when executing the stored procedure
- Document Source — Contains all available documents from Format steps previously created in the task; note that the document format is displayed after the step name
About the Main Tab
Use the Main tab to choose the relevant stored procedure for this task step.
Choose the OLEDB Connection used for this task step— these are the connections created in the global configuration.
The Call Stored Procedure (OLEDB) tool queries the database and retrieves all available stored procedures — click Browse to choose the required procedure. Where applicable, the database Schema Name of the selected stored procedure will be displayed.
Parameters associated with the procedure are displayed in this table. Drag-and-drop variables, recordset columns, or step properties to the Value column to map to the procedure parameters at task run-time. If any new parameters were added since the Call Stored Procedure (OLEDB) step was first added to the task, click Refresh to pull those into this task step.
Use Memory to memorise some or all of the step output for use in other task steps. This is particularly useful when the Call Stored Procedure (OLEDB) step is called multiple times — use Memory to compare the record or document previously processed to the new record or document currently being processed to avoid duplicates.
About the Recordset Tab
If the stored procedure selected in the Main tab only writes data to the database, the parameters in this tab can be ignored. If the stored procedure reads data, use this tab to check the recordset structure created by the Call Stored Procedure (OLEDB) tool:
Enable This procedure returns a recordset and click Execute. You are prompted for confirmation to execute the procedure; click Yes.
If the stored procedure requires parameter values to execute the procedure, you are prompted to enter them:
At this point, you cannot use variables, recordset columns, or step properties. In Value, type in the test parameter values where required. The procedure is executed and the results displayed in the Results tab:
with the Columns tab showing the returned recordset columns:
About the Options Tab
The Options tab allows you to define how errors in this step are handled at task runtime.
If an error occurs, you can decide whether the step should Continue processing, or terminate the step immediately (Abort Step).
If the step is aborted, you can choose to Continue processing onto the next step in the task, or terminate the whole task immediately (Abort Task).
