You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 23
Next »
In this article, you will learn how to transfer data from 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 a file to data in a database. There is no specific requirement on names. All that is necessary is that the columns belonging to a table in the database must be 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.
![](/docs/download/attachments/25428771/image2018-11-6_22-4-28.png?version=1&modificationDate=1541534497000&api=v2)
Preliminary Steps (Database preparation):
- Setup your Database for Integration ( For this Tutorial I will be using MySQL Database named "Accounts".)
- Create a Table "dbAccount" in Database. The scrips below show how to do it for MySQL, SQLSERVER and Oracle databases.
- Once you have created the table insert some dummy values on it.
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:
- Go to www.mydbsync.com, click on Customer Login. Enter your Username and Password and click on Development Studio to open the Project console.
- Create a project with project, process, workflow. Use names of your choice.
- 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).
- Click on Project ->Process -> Workflow
- From the Workflow section, proceed to the Trigger Section.
![](/docs/download/attachments/25428771/image2018-10-10_5-25-14.png?version=1&modificationDate=1539185298000&api=v2)
- In 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 Query by using the Advanced View section of Advanced Query Builder.
- In Rules section, select target connector as "Database", operation, target object respectively.
- Click on Map to see Mapping screen. You will see the list of Database fields in the left section. On the right section, in Schema, you see a list of selected File columns.
- 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.
- Ensure that Workflow status is On.
- You are now ready to run the sync. Click on the Run Button on the Top Right corner of the page.
- After the successful sync, open the log and the table. Check the file data have been rightly inserted into the database table.