DBSync Cloud Workflow (iPaaS)

2022

Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Next »


  • Setup your Database for Integration. For this Tutorial I will be using MySQL Database Named "salesforce".
  • Create tables "dbAccount" and "dbAccountNew" in Database with following script.
    Database Scripts
    create table `salesforce`.`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 `salesforce`.`dbaccountnew`
 (`AccountID` varchar (100) NOT NULL,
 `AccountName` varchar (300) NOT NULL,
 `BillingAddress` varchar (500),
 `AnnualRevenue` Decimal (50,2),
 `CreatedDate` datetime ,
 `LastModifiedDate` datetime ,
 `NumberOfOfficeLocations` int,
 PRIMARY KEY (`AccountID`)
 );
  • Enter some test data in "dbAccount" table. Go To www.mydbsync.com.
  • Select Customer Login. Enter your User Name and Password and click Sign in. Click on Launch to access DBSync Project page.
  • Click on Projects in the left panel, click on Create New Project, enter Project Name DatabasetoDatabase & Save.
  • Click on the project DatabasetoDatabase, click on Create New Process, enter Process Name Database2Database & Save*.*
  • Click on Connectors in the left panel, click on Create New Connector, enter Connector Name Database1, *select connector type *Database Connector & Save.
  • Click on the new Connector Database1, enter your database details in Connector settings.
  • Username, Password, Type, Host, Port fields should be updated with your database details, Database URL will get updated automatically.
  • Click on Save and then Validate Connection to confirm connection. 
  • Click on the process Database2Database, click on Create New Workflow, enter Workflow Name Execute & Save. 
  • Click on the workflow Execute, you will see two sections Trigger & Rules.
  • In Trigger section, you will see Query Builder, Advanced Query Builder & Properties.In Rules section, you will see a button to add new rule.
  • In Trigger section, you can use Query Builder when --- Updated till here
  • 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 presented with a query section will appear on the right of lookup icon. Query section will contain all the table listing from the database. Select the table and fields from the table to auto generate the query in query builder section. You can also write your own query. In this tutorial I will be selecting all the fields from "dbAccount" table. Click on save button once done. The screen shot is shown below:




  • Right Click\Properties on DBWriter. The properties will be displayed in the bottom left area of DBSync Console. Select Database from Adapter Drop Down. Once Database Adapter is selected, all the properties for DBWriter will be listed in the properties area with 2 lookup icons. Click on the lookup icon at the top and list of tables from database will be displayed in the query section on the right. 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 "dbAccountNew". Enter the primary key field name of the table in the pkey field, Select Upsert in Writetype and select true from autocreate drop down. A screenshot is shown below with all the values entered for DBWriter. Writetype contains 3 values in the drop down "insert", "update" and "upsert".
    • Insert: If this option is selected then only new records from the source table will be pushed to target data source.
    • Update: If this option is selected, it will update the target data source records with updated values in the source.
    • Upsert: Upsert is combination of insert and update. If this option is selected then new data will be inserted in target data and old data will be updated with changed data.






  • Click on Save once done. Right Click\Properties on DBMap. 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.





  • 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 at the end of this tutorial or you can refer the following link "Mapping Functions". The Map window with all mappings done is shown below.


  • No labels