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
AS
DECLARE @ActionType varchar(12)
DECLARE @TablePKValue varchar(12)
Set @ActionType=2;
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]
@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'
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
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'
BEGIN
delete from Customers where CustomerID=@CustomerID
delete from Interest where Rating=@CustomerID
END
Else if @Action='1'
BEGIN
IF @Rating IS NOT NULL
begin
DECLARE @TempInterestID int
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:
Step 3. Map schema
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.