Page History

In this article, you will learn how to transfer data from a database to a file, residing in an FTP server, to a table in a database. 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 file, to the data to / from in a filedatabase. There is no specific requirement on names. The columns All that is necessary is that the columns, belonging to a table in the database, must be matched mapped by the user to the columns defined in the file. For example, the below table matches the columns of a table named Account (see below Preliminary Steps) with the fields in the file.

The following is an example of a file that can be transferred to a database:

Image Added

After the transfer, you should have the following data in your database:

Image Added

1. Setup your Database for Integration ( For this Tutorial, I will be using MySQL Database named "Accounts".)
2. Create a Table "dbAccount" in Database. The scrips scripts below show how to do it for MySQL, SQLSERVER and Oracle databases.
3. Once you have created the table, insert some dummy values on in it.

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 perform the following steps:

1. Go to www.mydbsync.com and, click  click on CustomerLogin. Enter your Username and Password and click on Development Studio to open the Project console.
2. Create a project with a project, process, workflow. Use the names of your choice.
3. Click on Connectors on the left section of Project Console and create a DB and an FTP connector. (Note: Enter the required details in Database and FTP credentials in the fields and validate the connections). 
4. Click on Project ->Process -> Workflow
5. From the Workflow section, proceed to the Trigger Section.
   
   
   
   
6. In the Triggers section, you can use three tabs , - namely, Query Builder, Advanced Query Builder, & Properties. You need to select data-source as FTP. You can make additional changes to the Query by using the Advanced View section of the Advanced Query Builder.
7. In the Rules section, select target connector as "Database", operation, target object respectively.
8. Click on Map to see the Mapping screen. You will see the a list of Database fields in the left section. On In the right section, in Schema, you see a list of selected File columns.
9. Drag & drop the fields from Schema next to the required Database fields. Click on Save and Close once the mapping is completed. Now, click on Save Workflow.
10. Ensure that Workflow status is On.
11. You are now ready to run the sync. Click on the Run Button on the Top Right corner of the page. 
12. After the successful sync, open the log and the table. Check and make sure that the file data have has been rightly inserted in to into the database table.