How to get primary key back in response after insertion of record in Dell Boomi? - Tech:443

Latest

Tuesday 16 August 2022

How to get primary key back in response after insertion of record in Dell Boomi?



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.

 

  1. 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
  2. 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.

 

  1. Once your stored procedure is ready, open Boomi and import the stored_procedure through the database import wizard.
  2. 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.