Page History

...

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

...

• Wiki Markup
  Click on *\[X\]* icon on *Start_state* to de-link *Start_State* to *End_state.*

• Drag the Create State Button onto the control flow area to create a new state. Enter the state name as "DB2Customer". Similarly create another state called "DB2QBInvoice" and click on OK Button. There will be two new boxes/states in the Control flow area. Link all the states by first right-clicking on each box and left-click on the box to establish connection with. A screenshot below illustrates the same. 

Image Modified

• Once the connection is established between all the boxes / states, you can see an arrow connecting each state. This constitutes the Process Definition Language for your integration profile.

Accounts Mappings:

• Double-click on "DB2QBCustomer" state or right-click on the same and click on "Open Dataflow" to open the state.
• Drag Create Reader, Create Map & Create Writer into the center of the console and name each one as "DBReader", "DBmap" & "QBWriter" respectively. A screenshot below illustrates the same.
• Establish connection between Reader, Map and Writer by first right-clicking on a box and select "connect" and left clicking on the following box as shown.

...

• Enter the following query within the query section of the database adapter.  

 select * from account 

• Right-click on the DBwriter now and select "properties" and enter configure the following.

Table :  CustomerAddRq

maxbatchsize  :  1

writetype_ _ :  insert

autocreate  :  True

• Similarly right-click on the DBmap state now and select "properties". Click on* {}Edit{} link besides  *****_map.xml file to open the mapping page in a new window or a new tab. Enter the following mapping.
  
  || 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"

• At this point, your DBSync is set to move Account records from your database table to Quickbooks Customer.

Invoice Mappings:

• From the process builder page, double click on the SyncProcess and open the "DB2QBInovoice" state by double-clicking on the same.
• Now follow the same procedure as you did for Account Mappings by dragging Create Reader, Create Map & Create Writer to create DBreader, DBmap & DBwriter respectively and establish connection between the same.
• Configure the DBreader for the DB2QBInvoice state with the following code.

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

• Make sure the XML Formatter section is filled up with the following code.

  Code Block
  customerid,billaddress,billingcity,billingstate,billingpostalcode,billingcountry,date,invoice_no,[item,itemdesc,quantity,salesprice]

• Now configure the DBwriter 

Table :  InvoiceAddRq

maxbatchsize  :  1

writetype_ _ :  insert

autocreate  :  True

•  Now open the DBmap and enter the following mapping in the mapping window.

• In the above mappings, we are using the operator Type as Loop so that line items are grouped as a list. For more info on the loop operator, refer to the above XML Formatter link.

At this point, you have configured your DBSync profile to integrate both the Accounts and Invoice table that were created in your SQL Server. 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.