Stored Procedure Connector

This topic provides details of the Alemba® Stored Procedure Connector, including:

• The name of the .NET assembly file

• The connection methodology

• Use case scenarios

• Architectural and functional details that ensure the running of the use case through the Outbound Action task in the Workflow Platform

For compatibility and version support details, refer to the ASM Connector Matrix.

You should familiarize yourself with the information in Installing Connectors before installing any connectors, and read the Integration topics for more information on how to configure them.

Overview

The Alemba® Stored Procedure Connector allows values to be passed between a workflow in ASM and a stored procedure in the ASM database.

This connector:

• Sends values from a request or task to the incoming parameters of the stored procedure in the ASM database.

• Triggers the stored procedure to perform an action based on those values.

• Retrieves values from the outgoing parameters of the stored procedure and displays them in the request or task.

Use Case Scenario

Purpose	Stored procedures allow for SQL Statements and logic to be executed from a single call/command.
Role	The role of this connector is to pass parameters from the Outbound Action task to the Stored Procedure and receive return parameters.
Example	As part of an off-boarding workflow request, a child Outbound Action task calls a stored procedure to identify if there are any active CMDB Items still linked to the employee who is leaving, and return the value to the request or task.

Connector Description and Components

The table below provides a description of the Alemba® Stored Procedure Connector.

Information fields	Name
Connector	Stored Procedure <-> ASM Core workflow
Assembly	Alemba®.Conenctor.StoredProcedure.dll
Stored Procedure Exclusion File	StoredProcedure.xml
Connection Methodology	SQL Server Database

Connection Parameters

Installation

No install is required for this connector. Upon ASM Core install, this connector is visible in the Integration module, ready to be configured.

Additional Information

This connector does not include any Federated CMDB population functionality.

Connector Operation

Configuring a Source for the Stored Procedure Connector

1. Select ≡ > Admin > Integration.

2. In the Explorer pane, under Integration, select Sources.

3. Select the button on the toolbar. In the pop up window, select Alemba Stored Procedure Connector from the drop-down list.

4. In the Integration Source Details window, complete the details.

Parameter	Description
Name	Type a name for the integration source

5. Further fields are displayed on this window, but you do not need to complete them at this stage.

6. Select the button on the toolbar to test the connection.

7. Save the details.

This Source can be used in any Outbound Action task in any workflow template. No further Sources for this connector need to be created.

Configuring Outbound Action Tasks

This connector is designed to be used by Outbound Action tasks in workflows.

The information contained below relates directly, and only, to configuring task fields for the Stored Procedure Connector, and assumes a basic working knowledge of tasks in workflow templates.

For general information on configuring Outbound Action tasks, refer to Creating an Outbound Action Task.

In the Mapping Details section of the Outbound Action task, complete the details:

1. Expand the Mapping Details section, if necessary, and select the Source and Action.

Source

Select the Source configured for the Stored Procedure connector in the Integration Platform.

Action

Select the stored procedure to run. The list of stored procedures is populated from the ASM database and displays custom stored procedures and the standard stored procedure ALLOCATEUNIQUEREF. All other out-of-the-box stored procedures are intentionally excluded and unavailable. Deprecated stored procedures may be visible in the list. Additional stored procedures can be added to the exclusion list, to ensure they are not used. The exclusion list is contained in <SystemPath>/Config/Alemba®.Connector.StoredProcedure.xml

1. Select the Outgoing tab to map the fields for outgoing field values from ASM. This allows you to specify how field values from the ASM request or task correlate to the incoming parameters in the stored procedure when this task activates.

2. Click Add. In the Add Fields window, select the parameters (from the stored procedure) you want to map to fields on the task or parent request. Click OK.

   • On the browse table, click the corresponding cell in the Internal Field column. Then click the field selector button to pick a static value or a field from the task or request.

   • Depending on the field’s data type, additional options can be selected in the field selector, including: field transformations, profile maps, or resolution rules.

   • Add more field mappings if needed.

   • To delete any mapped fields, select the mapping and click Remove. In the warning dialog box, click Yes to remove the field mapping or No to cancel.

   • The Send Notify checkbox does not apply for the Stored Procedure Connector.

3. Select the Incoming tab to map the incoming values from the stored procedure. This allows you to specify how the values from the outgoing parameters in the stored procedure correlate to fields in ASM when a response is received from the stored procedure.

4. Click Add. In the Add Fields window, select the task or request fields you want to map to outgoing parameters in the stored procedure. Click OK.

   • On the browse table, click the corresponding cell in the Action Field column. Then click the field selector button to pick a static value or a parameter from the stored procedure. These fields are defined at the level of the connector for the selected integration source.

   • Depending on the field’s data type, additional options can be selected in the field selector, including: field transformations, profile maps, or resolution rules.

   • In the Update column, select the conditions for populating the field in ASM. Always always updates the ASM field with the value from the stored procedure. Only When Blank only populates the ASM field with the value of the stored procedure if the field in ASM is blank. This means that an existing value in ASM cannot be overridden.

   • The On Initial Population Only update condition does not apply.