Prerequisites

Before adding a Call Stored Procedure (OLEDB) The Call Stored Procedure (OLEDB) tool executes database stored procedures and functions to provide read, write, and automated data processing. task step, ensure you have met the following:

Adding an OLEDB Data Source Connection

Before you can use the Call Stored Procedure (OLEDB) tool in a task step, you must create the connection to an OLEDB database first.

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.

1. Click Add to create a new connection; select the required OLEDB driver or data source.
2. Expand OLEDB Drivers to see all OLEDB drivers available on the client computer.

   Note: Any computer hosting a BPA Platform client that runs tasks using this connection, including the BPA Platform server, must have the selected driver installed on the computer. Without the driver, the task will fail.

3. Once a driver has been selected, add the connection details to the database. Note that the configuration interface is different for every driver available.

For more information, see Adding OLEDB Connections.

Adding a New Task Step

Even though the Call Stored Procedure (OLEDB) tool is found under three different tool categories, they do not affect the functionality of the tool.

To add a new Call Stored Procedure (OLEDB) step to an existing task:

1. Open the relevant task (if creating a new task, see How to Create a Basic Task).
2. Either:

►

Click and drag the Call Stored Procedure (OLEDB) icon from the Task Browser to the task Design area.

–OR–

►

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).

3. In the General tab, name your task step and select the relevant input recordset or document.
   How Do I Do This?

   

   Click the image to view full size.

   The General tab is used to Name and describe (Description) this task step.

   Tip: If this task instance makes use of two or more Call Stored Procedure (OLEDB) steps, ensure the Name used is unique for each individual 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.

   For more information, go to the main General tab topic.

4. Go to the Main tab and choose the relevant stored procedure. Map the BPA Platform variables to the relevant stored procedure parameters.
   How Do I Do This?

   Use the Main tab to choose the relevant stored procedure for this task step.

   

   Click the image to view full size.

   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.

   Parameters associated with the procedure are displayed in the table below. 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.

5. If the store procedure returns a recordset, go to the Recordset tab to check the recordset structure, else go to the Options tab (next step).
   How Do I Do This?

   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.

   For more information, go to the main Recordset tab topic.

6. Go to the Options tab and select the relevant error handling options for this task step.

   How Do I Do This?

   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).

7. Click  to save the task step.