Database to Salesforce Integration

Database to Salesforce Integration - MySQL as Master Database

• Setup your Database for Integration. For this Tutorial I will be using MySQL Database Named "salesforce".

• Create a Table "dbAccount" in Database with following script. If you want you can enter your custom values and skip insert queries.

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 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")
 );

• Go to www.mydbsync.com
• Select Customer Login. Enter your User Name and Password and click Login Button.

          (Note: If you are using hosted version of DBSync then after login Go to My Home and click on Launch Button in Console Area).

• In DBSync console click on Edit Link for "Salesforce Database Bidirectional" Profile. If the profile does not exist, go to Library from top menu and click on Add Button in front of "SalesforceDatabase: Bidirectional" and the profile will be added in DBSync Console.

• Click on Edit icon for Salesforce adapter and enter the settings (User Name & Password) adapters at the bottom of the screen and click on Save and validate them by clicking validate button in front of the adapter.

• Click on Edit icon for Database adapter and enter the settings (URL, User Name & Password) adapters at the bottom of the screen and click on Save and validate them by clicking validate button in front of the adapter.

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

• Double Click on SyncProcess on right side of the screen as indicated below in the image. You will be redirected to Process Builder Window.

• Sync Process window shows 3 processes by default. Start_state from where the sync starts, then control moves on to Execute state. Execute state contains all the processes for Sync. And Finally End_state refers to a state where the sync ends. In the process Builder window, Double click on Execute Process as Indicated below. You will be redirected to Data Flow window. 

• In Execute State you will find 4 states as shown in the image below with the data flow sequence, by default Named

         1. Reader:
         2. Map:
         3. Writer:
         4. Status:
        Reader: It reads the data from source adapter. In a state there should be only one reader.

        Map: Map is where data is transformed to be used by writer process. We can have more than one MAP in one state. One reader can connect to 10 Maps.

        Writer: This component writes the data to target data source. We can have more than one Writer in a state.

        Status: This component keeps the status data, In case you are Writing back into Source Data Source. Otherwise its not required for sync process.

       In the Data Flow Window, Right Click Properties on Reader. And at the bottom you will be presented with Reader properties at the bottom of the screen.

• Select Database from the Adapter Drop Down. You will be presented with Reader properties at the bottom of the screen. A Lookup button will appear, click on the lookup button and you will be provided with the list of tables in Query section. Select the Table Name and Columns and the query will be auto generated. In this example I have used MySQL functions DATE_FORMAT() with the query. To push MySQL Date values to Salesforce we need to change its format. For this purpose you can use following MySQL function with your query to get date for Salesforce format.

          DATE_FORMAT(columnname,'%Y-%m-%dT%H:%i:%sZ')
          Once done Click on Save Button.

• In the Data Flow Window, Right Click Properties on Writer. Select Database from the Adapter Drop Down. A Lookup button will appear, click on the lookup button and you will be provided with the list of tables in Query section. Select the Table Name in Salesforce and enter External ID (In this case I have created a custom field in Salesforce Account Object named DatabaseID__c) in "externalid" field. From writetype Drop Down, select Upsert. Writetype has 3 values

         1. Insert
         2. Update
         3. Upsert
         Insert is used if you only need to insert the new records from database into Salesforce.

         Update will update the existing records in Salesforce with new data for those records.

         Upsert is combination of insert and update; it will insert new records into Salesforce and update the existing records with updated data. For this demo I will be using Upsert.

         Enter maxbatsize value, for this demo I will be using maxbatsize of 2. maxbatchsize decides how many rows will be read and written in a batch. Once done Click on Save Button.

• In the Data Flow Window, Right Click Properties on MAP process*.* Click on Edit Link for ********_map.xml file. You will be presented with a new window to create your mappings. Both the windows are shown below sequentially.

• Click on Target Column followed by Source Column to create mapping between them. You can use various functions provided by DBSync to customize the data provided by source field. Once you are done click on Close Button at top right of the screen. You will move back to Data Flow window of DBSync. You will find list of functions and their use at the end of this tutorial.

Note: In the mappings I have used 3 custom Fields from Salesforce (target in this tutorial). In Salesforce Account ID, Created Date and Modified Date are standard fields and are not editable. So to write data of Database fields "AccountID", "CreatedDate" and "LastModifiedDate" these 3 custom fields have been used. So when you map data from Database to Salesforce always make sure that you have write access to all the fields you are using in the mapping.

• Click [x] icon in the Writer: process to delete the control flow from Writer: to Status: process. Right Click 
   Remove on Status: to remove it from Data Flow window as it’s not required for sync process. After removing it the data flow will remain as shown below:

• In the Data Flow window, click on Advanced Properties Tab at the bottom. Select Log Level as "All" from drop down and enter your E-mail in Email field (All logs/errors will be emailed to this ID) for Log. Click on Save button below Email Field to save these settings.

• You are now ready to run the sync. Click on the Run Button in Properties Section and Sync will start. You will be presented with a Run Status message box as shown below:

• You will receive an Email from dbsync2@avankia.com once the sync is complete. Check your Salesforce.com instance and you will see all the accounts moved from Database to Salesforce as shown below:

Mapping Functions