Problem:
Boomi’s native Database connector doesn’t return any response to the process after insertion or any database-related operation.
Solution:
This problem can be solved by using a Stored Procedure. It allows us to return whatever field we want after completing the database operation.
- Steps: Open SSMS (Microsoft SQL Server Management Studio). It is a free software. Then Click on Databases -> [YOUR_DB_NAME] -> Programmability -> Right Click Store Procedures -> New
- Write the stored procedure select the code and execute. Example :
USE [YOUR_DB_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [schema].[STORED_PROC_NAME]
@ORDER_ID varchar(50),
@TRANSACTION_ID varchar(50),
@EMAIL varchar(50),
@SUPPLIER_ID varchar(50),
@ID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [YOUR_TABLE_NAME]
VALUES(
@ORDER_ID varchar(50),
@TRANSACTION_ID varchar(50),
@EMAIL varchar(50),
@SUPPLIER_ID varchar(50),
);
SELECT @ID=SCOPE_IDENTITY();
END
We just need to add OUTPUT for the output variable while declaring in the stored procedure. We can have more than one output.
SCOPE_IDENTITY(); = Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.
- Once your stored procedure is ready, open Boomi and import the stored_procedure through the database import wizard.
- Once the profile is imported through the wizard you will need to map @RETURN_VALUE to your output which is nothing but the ID on which insertion took place.
