DBSync Cloud Workflow (iPaaS)

2022

Page tree

Versions Compared

Old Version 8

changes.mady.by.user Vishwanath B

Saved on

compared with

New Version Current

changes.mady.by.user Munira Majumdar

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Panel

Sage Intacct to Database Integration

  • Setup your Database for Integration. For In this Tutorial I am using Oracle Database named "Sage IntacctDB".
  • Create table "Account" in Database with following script.

Database Script : MYSQL

Database Script : SQLSERVER

Database Script : Oracle

create table

`IntacctDB`

`Sage IntacctDB`.`dbAccount`

(`AccountID` varchar(100) NOT NULL ,
`AccountName` text NOT NULL ,
`BillingStreet` text ,
`BillingPostalCode` numeric(20) ,
`AnnualRevenue` decimal(30,2) ,
`CreatedDate` date ,
`LastModifiedDate` datetime ,
`NumberOfOfficeLocations` int ,
PRIMARY KEY (`AccountID`) );

create table dbAccount

(AccountID varchar(100) NOT NULL ,
AccountName text NOT NULL ,
BillingStreet text ,
BillingPostalCode numeric(20) ,
AnnualRevenue decimal(30,2) ,
CreatedDate date ,
LastModifiedDate datetime ,
NumberOfOfficeLocations int ,
PRIMARY KEY (AccountID) );

CREATE TABLE "SYS"."DBACCOUNT"

("ACCOUNTID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"ACCOUNTNAME" VARCHAR2(100 BYTE),
"BILLINGSTREET" VARCHAR2(50 BYTE),
"BILLINGPOSTALCODE" VARCHAR2(50 BYTE),
"ANNUALREVENUE" NUMBER(30,2),
"CREATEDDATE" DATE,
"LASTMODIFIEDDATE" VARCHAR2(40 BYTE),
"NUMBEROFOFFICELOCATIONS" NUMBER(*,0),
CONSTRAINT "DBACCOUNT_PK" PRIMARY KEY ("ACCOUNTID")
);

  • Enter your DBSync username Username and password Password to login. Click on Launch to navigate to DBSync the Project console page.
  • Once into the DBSync console, click on Create profile Button, Enter Profile Name and click on Save Button as shown.

Image Removed
Image Removed

  • A new Profile Click on Project on the left section of the console. Click on Create New Project. Enter project name as Sage Intacct-Database and click Save.
  • A new Project will be created by the given Name. In this tutorial I have used name Intact-Database. The Profile will be listed in the profile area as shown below:

Image Removed

  • Click on Manage - Edit link on the right side of the profile Name. You will be redirected to the Process Builder window in the DBSync Console.

Image Removed

  • Click the Create Adapter Button on the left hand side.

Image Removed

  • Enter the Adapter Name, from AdapterType Drop Down Select Intacct Adapter and Click on Save Button.

Note: Adapter Name cannot contain Blank Space. 

Image Removed

  • Click on the Create Adapter button on the left hand side. Enter the Adapter Name, from AdapterType Drop Down Select Database Adapter and Click on Save Button.

Image Removed

  • Adapter will be created and will be listed in the Adapter section of the Process Builder.
  • Click on Edit link next to the Intacct adapter and you will be presented with properties for the Adapter at the bottom left of the window. Enter values for all the properties and click on Save Properties. Both the sections of the window are shown below sequentially.

Image Removed

Image Removed

  • Click on Validate link Next to Intacct Adapter. A message box is displayed; if the message says "Connection Settings are valid" you are good to go. Else Click on Edit and enter correct values for Adapter properties and repeat the steps. Click OK.

Image Removed

  • Click on Edit link next to the Database adapter and you will be presented with properties for the Adapter at the bottom left of the window. Enter values for all the properties and click on Save Properties. Both the sections of the window are shown below sequentially.

Image Removed

Image Removed

  • Click on Validate Link Next to Database Adapter. A message box is displayed; if the message says "Connection Settings are valid" you are good to go. Else Click on Edit and enter correct values for Adapter properties and repeat the steps. Click OK.

Note : If you are using a different Database server, you can enter the connection string for the same as shown here.

Image Removed

  • Click on Add Process Icon on the Right side of the screen. Enter Process Name and Click on Ok. A new process will be created and will be listed under profile name on the right side of the screen.

Image Removed

Image Removed

  • Double Click on the Process Name "IntacctToDataBaseSync" to open the process in the Control flow for Intact to Database Sync process. The process will contain 2 States by default:
  1. Start_state
  2. End_state

Image Removed

  • Drag the Create State Button onto the control flow Area to create a new state. Enter the state name as "Execute" and click on OK Button. A new state will be created and placed on control flow area.

Image Removed

Image Removed

  • Click on 
    Wiki Markup
    *\[X\]*
     icon on Start_state to delete the default control flow from Start_State to End_state. Right Click and click on connect on Start_state followed by left click on Execute will create a control flow from Start_state to Execute. Right Click and click on connect on Execute followed by left click on End_state will create a control flow from Execute to End_state. All the control flows are displayed using arrows from one state to other state. Below you will find 2 screenshots one containing no control flow and second containing control flow as Start_state to Execute to End_state. Once done click on Save button at the top right corner of the window.

Image Removed

Image Removed

  • Double click on Execute state to open data flow for Execute state. Drag the Reader button onto the data flow area and enter the Name for reader and click on OK Button. In this tutorial I am using "IntacctReader" as the reader name.

Image Removed

  • Follow the same step for Create Map and Create Writer button. And you will get a data flow area as shown below in the screenshot. In this tutorial I have used "IntacctMap" for Create Map and "DBWriter" for Create Writer component.** Reader: Reader is a process component that reads data from source "data source". Reading the data is also known as "Extracting" the data. Note one state can have only one Reader.** Map: Map component converts data into the format that could be understood by writer and target data source. Converting the data so that it could be understood by target data source is also known as "Transforming" the data. Note One state can have more than one Map.** Writer: Writer component writes the data to target "data source". Writing the data into a data source is also known as "Loading" the data. Note One State can have more than one writer.

Image Removed

  • Right Click on IntacctReader and click on Connect followed by left click on IntacctMap to create a control flow from IntacctReader to IntacctMap. Right Click on IntacctMap and click on Connect followed by left click on DBWriter to create control flow from IntacctMap to DBWriter. All control flows will be displayed using arrows. A screenshot is shown below displaying the control flow after following the above process.

Image Removed

  • Right Click on IntacctReader and click on Properties. The properties will be displayed in the bottom left area of DBSync Console. Select IntacctAdapter from Adapter Drop Down. Once IntacctAdapter is selected, all the properties for IntacctReader will be listed in the properties area with lookup icon. Click on the lookup icon and list of objects from Intacct will be displayed in the query section on the right. Query section will contain all the table listing from Intacct. Select Intacct entities and fields from the list to auto generate the query in query builder section. You can also write your own query. In this tutorial I am selecting few fields from "customer" entity of Intacct. Click on save button once done. The screen shot is shown below:

Image Removed

Image Removed

  • Right Click on DBWriter and click on Properties. The properties will be displayed in the bottom left area of DBSync Console. Select Database from Adapter Drop Down. Once Database Adapter is selected, a query builder section will appear in properties area and a lookup icon on the right side of query builder section. Click on lookup icon and you will be presented with a query section which will appear on the right of lookup icon. Check on the target table (The table you want to write the data to) name on the right side of the window. In this tutorial my target table is "Account". Select insert in Writetype and click on Save button to save the properties.

Image Removed

  • Right Click on IntacctMap and click on Properties. Enter 1 in the sequence text box. Enter description for the mapping. Click on Edit link for *******_map.xml file. You will be presented with a new window with source and target columns. Screenshots for Map properties and map editor is shown below respectively.

Image Removed

Image Removed

  • In the Map window click on Target column followed by source column to create field to field mapping. You can also use various functions provided by DBSync. In the mappings shown below I have used few of these functions. Rest of the functions and their details you can find in the wiki link "Mapping Functions". The Map window with all mappings done is shown below.

Image Removed

  • Once you are done close the mapping screen from close button on the top right. In the DBSync Console available in the previous tab of your browser click the RUN button to run the sync.

Image Removed

  • You can view the logs by clicking on Profiles on top right corner and then Log Viewer. 

Image Removed

  • The logs will be displayed as show below. Click on the required message to view the details. 

...

  • Click on Create New Process. Enter process name as Sage IntacctToDatabase. Click Save to save the process.
  • Click on Connectors on the left section of the Project console. You need to create new Connectors for Database & Sage Intacct.
  • Click on Create New Connector. Enter the Connector name as Sage Intacct. Select the Connector type as Sage Intacct Adapter and click Save.
  • Enter all the required details, click Save. Then, Validate connection.
  • Sage Intacct Connector is created and the connection is validated. Next, Database adapter needs to be created.
  • Click on Connectors on the left section of Project console. Click on Create New Connector.
  • Enter connection name as Database and select the Connector type as Database Adapter. Click Save.
  • Enter all the required details. Click Save. Then, Validate connection.
  • Click on Projects. Then, click on the project Sage Intacct-Database. Here, you will see the new process Sage IntacctToDatabase. Click on the newly created process Sage IntacctToDatabase.
  • Using the button Create New Workflow, create the workflow "Execute".
  • Click on workflow Execute. You will see two sections: Trigger on the top; and, Rules on the bottom.
  • In the Trigger section, go to Advanced Query Builder and select Datasource as Sage Intacct.
  • Click on Switch to Advanced view and enter the following query in the Query Builder. Click Save and Close.
Code Block
select CustomerID, ignoreduplicates, name, parentid, shippingmethod, custtype from customer
  • In the Rules section of a workflow, click on Add New Rule. Select Target Connector, Operation, Target Object as Database, Insert and Account, respectively.
  • Click on the Map in this newly created Rule. You will see a mapping screen.
  • In the mapping screen, you will see a list of target fields on the left section. On the right, you will see the source fields under Schema.
  • Drag and drop the required fields from Schema into the required source fields.
  • Once you are done, close the mapping screen from Save and Close button on the top right. Save the workflow and make sure that the status of workflow is On.
  • You can run the integration using the Run button; or, the Scheduler on the left section of the Project console. Run button is available once you open the Project.
  • Using the button Logs on the left section, you can view the Logs.
  • This concludes brief introduction on how to build DBSync integration data flow from Sage Intacct to Database.