Calling Stored Procedures in Dell Boomi: A Step-by-Step Guide

As an enterprise-grade integration platform (iPaaS), Dell Boomi simplifies integration across various systems, applications, and data sources. Boomi excels at handling complex data flows, including the ability to interact with database-stored procedures. This blog will cover how to call stored procedures within your Dell Boomi processes.

What are Stored Procedures?

Stored procedures are pre-compiled blocks of SQL code that reside directly within a database. They offer advantages such as:

• Performance: Stored procedures, which live on the database server, can execute complex queries and logic with optimized performance.
• Security: Stored procedures can enforce data access restrictions by encapsulating logic and reducing the risk of direct SQL manipulation.
• Maintainability: Stored procedures help centralize logic, simplify updates, and reduce the changes needed across multiple applications.

Prerequisites

Before diving in, ensure you have:

1. Dell Boomi Account: A Boomi account with the appropriate license for database interactions.
2. Database Access: Connection details for the database where your stored procedure resides (e.g., SQL Server, Oracle, MySQL)
3. Stored Procedure: An existing stored procedure within your database ready to be called.

Steps

1. Create a Database Connector
   • Navigate to Setup > Connectors and click “Create”.
   • Choose the appropriate database connector type (e.g., “Microsoft SQL Server”).
   • Provide connection details: hostname, port, database name, username, and password.
   • Necessary: Create a specific database operation to call your stored procedure. This involves selecting the stored procedure’s name and defining input and output parameters.
2. Utilize the Database Connector in your Process
   • Drag and drop the previously created database connector onto the canvas in your Boomi process.
   • Configure the operation to call the stored procedure defined earlier.

• Input Parameters: Supply any necessary input parameters to the stored procedure. These may come from previous steps in your process or user input.

1. Handle Output (Optional)

• If your stored procedure returns data (as opposed to simply executing an action), map this data to other components or store it locally within your process.
• If your stored procedure uses output parameters, you can access them to obtain results.

Example (SQL Server Stored Procedure)

Let’s say you have an SQL Server stored procedure named GetCustomerDetails with an input parameter of CustomerID. Here’s a process outline:

1. Database Connector (SQL Server)
   • Use your existing connector or create a new one.
   • Add an operation named GetCustomerDetails.
   • Map the CustomerID from your process as an input parameter.
   • Define output parameters corresponding to the data structure returned by the stored procedure.
2. Map Shapes
   • Use map shapes to extract data from previous steps (e.g., a web service call that obtained the CustomerID). Map this data into the database connector’s input parameters.
   • After the database connector, use additional map shapes to transform and route the stored procedure’s output for downstream use in your process.

Important Considerations

• Error Handling: Implement robust error handling to catch database exceptions or invalid data scenarios.
• Security: Consider the permissions granted to the database user associated with your Boomi connector. When feasible, use a limited-access user.

That’s it! You’ve now learned the fundamentals of calling stored procedures in Dell Boomi.