Page History

DBSync – Database to Quickbooks Integration

1. Configure your Database for Integration with your Quickbooks file. 
2. For this Tutorial I will be using SQL Server Database Named "DBSyncDB" and 2011 US Edition of Quickbooks.
3. Create tables "account" and "invoice" in Database with following SQL script.

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.

1. Open www.mydbsync.com in your browser and click on Customer Login.
2. Enter in your DBSync username and password to Sign in. Once logged in, click on Launch to access DBSync Integration Console.

Image Removed

1. Once logged into the DBSync console, click on Create

...

1. New project button .

Image Removed

1. You can use any naming convention for a

...

1. project as long as there are no white spaces in the name of the

...

1. project.
2. Once the

...

1. project is created, click on

...

1. Connector --- Edit to configure the

...

1. Connectors.

Image Removed

1. Click on the Create

...

1. New Connector from the top

...

Image Removed

1. right section of the page 
2. Enter the name for the

...

1. connector and select Database Adapter from the drop down. Click on Save button once done. 

Image Removed

1. Similarly, click on the Create

...

1. New Connector button again to add Quickbooks

...

1. Connector to your DBSync profile.
2. Once both the

...

1. Connectors are added, you will see them listed

...

1. in the

...

1. connector listing page

...

1. .

Image Removed

1. Now click on the Edit link next to the Database

...

1. Connector and enter your Database credentials as shown. In this tutorial, the database used is SQL Server 2005 and corresponding credentials are entered.

Image Removed

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 the Edit link next to the Quickbooks adapter in the console page.
• Under the file section, enter the full path of your Quickbooks file as shown.
  

...

• Please enter your DBSync password in the password section of the Web Connector and save it. 
• At this point you have successfully completed configuring your Quickbooks with DBSync. You can run the integration by clicking on the Update Selected button.

Custom Mapping for Account and Invoice tables.

• Click on Manage — Edit to navigate to adapter configuration page.

...

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

...

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

...