2022
In this article you will learn how to transfer data from a database to a file residing in an FTP server. First, you will see a data flow diagram, a process map and a field mapping. Then you will learn the preliminary steps necessary to prepare a database. Finally, you will see how to create your integration.
In this case we are matching data from / to a database to data to / from a file. There is no specific requirement on names. The columns belonging to a table in the database must be muched by the user to the columns defined in the file. Following the example below defined, we will match the columns of a table named Account, which contains fields that resemble the fields in the table Accounts in Salesforce.
BSync's process will match each of the field tables with the database tables as defined in the Data Map. In our example:
You can map any of the table columns to any of the file columns. However, it is advisable to keep the nomenclature. I our example we will match similarly to what we defined in our data map.
Database Script : MYSQL | Database Script : SQLSERVER | Database Script : Oracle |
create table `Accounts`.`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") ); |
Once you have your database ready, do the following steps: