Using the BizTalk SQL Adapter to synchronize data

Introduction

In my previous post, we used UPDATEGRAM to sync data. In this tutorial, we use a trigger to monitor the actions in the source database and create a new table named ACTIONHISTORY to store the actions including the action type, table name, primary key, key value, and status.

BizTalk server captured updated records by using a Stored Procedure. Then, BizTalk calls the target Stored Procedure with these parameters to update the records in the target database.

Using the code

First, we create a trigger to monitor the actions. Here we use UPDATE, for example.

Create TRIGGER [dbo].[Tri_Monitor_Customers_Update]
ON [dbo].[Customers]AFTER Update

-- Here is the statement to actually see the text of the trigger.

AS 
DECLARE @ActionType varchar(12)
DECLARE @TablePKValue varchar(12)

Set  @ActionType=2;  --perform to updated action

INSERT INTO [SqlAdapterTest].[dbo].[ActionHistory]
           ([TableName]          
           ,[ActionType]           
           ,[TablePK]           
           ,[TablePKValue]            
           ,[ActionStatus])
select 'Customers',@ActionType,'CustomerID',CustomerID,'New' from inserted

Create a Stored Procedure to Extract the Updated Records

Create PROCEDURE [dbo].[SP_Action_List_Customer]
      -- Add the parameters for the stored procedure here
@Rating as nvarchar(10)
as 
declare @ActionType as nvarchar(10) 
declare @TableName as nvarchar(10)
declare @TablePk as nvarchar(10)
declare @TablePKValue as nvarchar(10)
declare @ActionID as nvarchar(10)

BEGIN

 select top 1  @ActionID=ActionID,@ActionType=ActionType,
     @TablePKValue=TablePKValue from ActionHistory where ActionStatus='New'
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.if @ActionID is not null
           begin
               update ActionHistory set ActionStatus='Used' where ActionID=@ActionID
                   begin
                   if @ActionType='1'
                   SELECT @ActionType as ActionType,
                       *from dbo.Customers where CustomerID=@TablePKValue for xml auto
                   end

                   begin
                   if @ActionType='2'
                   SELECT @ActionType as ActionType,
                       *from dbo.Customers where CustomerID=@TablePKValue for xml auto
                   end

                   begin
                   if @ActionType='0'
                  
                   SELECT top 1 @ActionType as ActionType,
                       CustomerID=@TablePKValue from dbo.Customers for xml auto
                   end
            end
  else   
    /* no new customer, return an empty set */
    select @ActionType as ActionType,* from Customers where 1 = 0 for xml auto
    SET NOCOUNT ON;

END

In the target database, we create a Stored Procedure too. Here we take DELETE and INSERT actions, for example.

begin transaction 

begin try 

if @Action='0'  --Delete 

      BEGIN 
       delete from  Customers where CustomerID=@CustomerID 
       delete from  Interest  where Rating=@CustomerID--to delete othertable 
     END 

Else if @Action='1' --Insert 

BEGIN 

/*BEGIN Interest table*/ 
      IF @Rating  IS NOT NULL 

begin 
            DECLARE @TempInterestID int  --Check the item 
                  SELECT @TempInterestID = count(1) FROM [Interest] WHERE Rating=@Rating 

            if @TempInterestID=0 

            begin 
                  INSERT INTO [Interest]( 
                  [Rating],[InterestRate] 
                  )VALUES( 
                  @Rating,@InterestRate 
                  ) 
            end 
            else

So far, we have created a procedure to capture and update data. Similar to the previous post in part 1, we should create a BizTalk project first. The steps involved for this are:

• Step 1: Create a Biztalk project in Visual Studio
• Step 2: Add the generated items using the SQL Adapter
• Step 3: Map the schema
• Step 4: Configure the orchestration
• Step 5: Rebulid and deploy the project
• Step 6:Configure the application in the BizTalk Server 2009 Administrator Console.

The following is a screenshot of the steps:

Summary

This tutorial illustrates the synchronization between databases using the BizTalk SQL Adapter. With the BizTalk SQL Adapter, we can sync multiple tables or multiple records between the DB or the ERP, and so on.