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