Page History

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.

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 below show how to do it for MySQL, SQLSERVER and Oracle databases.
3. Once you have created the table insert some dummy values on 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 the following steps:

...