2022
USE [DBSyncDB] CREATE TABLE [dbo].[account]( [id] [varchar](20) NULL,[Name] [varchar](50) NULL,[firstname] [varchar](50) NULL,[lastname] [varchar](50) NULL, [phone] [nchar](20) NULL,[fax] [nchar](20) NULL,[billingstreet] [varchar](200) NULL,[billingcity] [varchar](50) NULL, [billingstate] [varchar](50) NULL,[billingcountry] [varchar](50) NULL,[billingcode] [varchar](50) NULL,[company] [nchar](100) NULL ) ON [PRIMARY] GO USE [DBSyncDB] CREATE TABLE [dbo].[invoice]( [invoice_no] [nchar](10) NULL,[customerid] [nchar](100) NULL,[item] [nchar](100) NULL,[itemdesc] [nchar](100) NULL, [quantity] [nchar](10) NULL,[uom] [nchar](50) NULL,[salesprice] [nchar](10) NULL,[total] [nchar](10) NULL, [billaddress] [nchar](100) NULL,[billingcity] [nchar](100) NULL,[billingstate] [nchar](100) NULL, [billingpostalcode] [nchar](10) NULL,[billingcountry] [nchar](100) NULL,[date] [nchar](50) NULL ) ON [PRIMARY] GO
CREATE TABLE `dbsyncdb`.`account` (`id` VARCHAR(20) DEFAULT NULL,`Name` VARCHAR(50) DEFAULT NULL,`firstname` VARCHAR(50) DEFAULT NULL, `lastname` VARCHAR(50) DEFAULT NULL,`phone` VARCHAR(20) DEFAULT NULL,`fax` VARCHAR(20) DEFAULT NULL, `billingstreet` VARCHAR(200) DEFAULT NULL,`billingcity` VARCHAR(50) DEFAULT NULL,`billingstate` VARCHAR(50) DEFAULT NULL, `billingcountry` VARCHAR(50) DEFAULT NULL,`billingcode` VARCHAR(50) DEFAULT NULL,`company` VARCHAR(100) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8; /*Table structure for table `invoice` */ CREATE TABLE `dbsyncdb`.`invoice` (`invoice_no` CHAR(10) DEFAULT NULL,`customerid` CHAR(100) DEFAULT NULL,`item` CHAR(100) DEFAULT NULL, `itemdesc` CHAR(100) DEFAULT NULL,`quantity` CHAR(10) DEFAULT NULL,`uom` CHAR(50) DEFAULT NULL,`salesprice` CHAR(10) DEFAULT NULL, `total` CHAR(10) DEFAULT NULL,`billaddress` CHAR(100) DEFAULT NULL,`billingcity` CHAR(100) DEFAULT NULL, `billingstate` CHAR(100) DEFAULT NULL,`billingpostalcode` CHAR(10) DEFAULT NULL,`billingcountry` CHAR(100) DEFAULT NULL, `date` CHAR(50) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE "SYS"."ACCOUNT" ("id" VARCHAR(20 BYTE) DEFAULT NULL,"Name" VARCHAR(50 BYTE) DEFAULT NULL,"firstname" VARCHAR(50 BYTE) DEFAULT NULL, "lastname" VARCHAR(50 BYTE) DEFAULT NULL,"phone" VARCHAR(20 BYTE) DEFAULT NULL,"fax" VARCHAR(20 BYTE) DEFAULT NULL, "billingstreet" VARCHAR(200 BYTE) DEFAULT NULL,"billingcity" VARCHAR(50 BYTE) DEFAULT NULL,"billingstate" VARCHAR(50 BYTE) DEFAULT NULL, "billingcountry" VARCHAR(50 BYTE) DEFAULT NULL,"billingcode" VARCHAR(50 BYTE) DEFAULT NULL, "company" VARCHAR(100 BYTE) DEFAULT NULL ); /*Table structure for table `invoice` */ CREATE TABLE "SYS"."INVOICE" ("invoice_no" CHAR(10 BYTE) DEFAULT NULL,"customerid" CHAR(100 BYTE) DEFAULT NULL,"item" CHAR(100 BYTE) DEFAULT NULL, "itemdesc" CHAR(100 BYTE) DEFAULT NULL,"quantity" CHAR(10 BYTE) DEFAULT NULL,"uom" CHAR(50 BYTE) DEFAULT NULL,"salesprice" CHAR(10 BYTE) DEFAULT NULL, "total" CHAR(10 BYTE) DEFAULT NULL,"billaddress" CHAR(100) DEFAULT NULL,"billingcity" CHAR(100) DEFAULT NULL, "billingstate" CHAR(100 BYTE) DEFAULT NULL,"billingpostalcode" CHAR(10 BYTE) DEFAULT NULL,"billingcountry" CHAR(100 BYTE) DEFAULT NULL, "date" CHAR(50 BYTE) DEFAULT NULL);
Note : Make sure both the tables have sample data in them before they can be mapped within DBSync.
Note : If you are using a different Database server, you can enter the connection string for the same as shown here.
11.Click on Validate link for the Database adapter. If the credentials entered are accurate, you will get a pop-up message saying the "Connection Settings are Valid".
12. At this point you have successfully validated your Database Adapter.
Click on [X] icon on Start_state to de-link Start_State to End_state.
Note: Always establish connection between states in the sequence of Reader: — Map: — Writer:
select * from account
Table : CustomerAddRq
maxbatchsize : 1
writetype_ _ : insert
autocreate : True
Target |
= |
Source |
---|---|---|
CustomerAddRq/CustomerAdd/BillAddress/Addr1 |
= |
VALUE("billingstreet") |
CustomerAddRq/CustomerAdd/BillAddress/City |
= |
VALUE("billingcity") |
CustomerAddRq/CustomerAdd/BillAddress/Country |
= |
VALUE("billingcountry") |
CustomerAddRq/CustomerAdd/BillAddress/PostalCode |
= |
VALUE("billingcode") |
CustomerAddRq/CustomerAdd/BillAddress/State |
= |
VALUE("billingstate") |
CustomerAddRq/CustomerAdd/CompanyName |
= |
VALUE("company") |
CustomerAddRq/CustomerAdd/Fax |
= |
VALUE("fax") |
CustomerAddRq/CustomerAdd/FirstName |
= |
VALUE("firstname") |
CustomerAddRq/CustomerAdd/IsActive |
= |
"true" |
CustomerAddRq/CustomerAdd/LastName |
= |
VALUE("lastname") |
CustomerAddRq/CustomerAdd/Name |
= |
VALUE("Name") |
CustomerAddRq/CustomerAdd/Phone |
= |
VALUE("phone") |
VALIDATEROW |
= |
"true" |
select invoice_no,invoice_no as invoicenumber,customerid,item,itemdesc,quantity,uom, salesprice,total,billaddress,billingcity,billingstate,billingpostalcode,billingcountry, convert(varchar,cast(date as datetime),126) as 'date' from invoice
customerid,billaddress,billingcity,billingstate,billingpostalcode,billingcountry,date, invoice_no,[item,itemdesc,quantity,salesprice]
Table : InvoiceAddRq
maxbatchsize : 1
writetype_ _ : insert
autocreate : True
Target |
= |
Source |
---|---|---|
InvoiceAddRq/InvoiceAdd/BillAddress/Addr1 |
= |
VALUE("billingaddress") |
InvoiceAddRq/InvoiceAdd/BillAddress/City |
= |
VALUE("billingcity") |
InvoiceAddRq/InvoiceAdd/BillAddress/Country |
= |
VALUE("billingcountry") |
InvoiceAddRq/InvoiceAdd/BillAddress/PostalCode |
= |
VALUE("billingpostalcode") |
InvoiceAddRq/InvoiceAdd/BillAddress/State |
= |
VALUE("billingpostalcode") |
InvoiceAddRq/InvoiceAdd/CustomerRef/FullName |
= |
VALUE("customerid") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd |
loop |
"invoice_no/list" |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Desc |
= |
VALUE("itemdesc") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/ItemRef/FullName |
= |
VALUE("item") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Quantity |
= |
VALUE("quantity") |
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Rate |
= |
VALUE("salesprice") |
InvoiceAddRq/InvoiceAdd/IsPending |
= |
"true" |
InvoiceAddRq/InvoiceAdd/TxnDate |
= |
VALUE("date") |
VALIDATEROW |
= |
"true" |
At this point, you have configured your DBSync profile to integrate both the Accounts and Invoice tables that were created in your SQL Server Database. A similar procedure can be followed for other databases like MYSQL,Oracle,DB2 etc. The only thing that would be changing within DBSync would be the connection strings for the respective databases.