topic Re: [resolved] Read files from ftp site and load to postgresql db in Talend Studio

[resolved] Read files from ftp site and load to postgresql db

Anonymous — Tue, 16 Dec 2014 10:13:44 GMT

Hi ,
I have a small requirement to perform. Can any one help me with the steps to perform this.I dont know how to log the list of files in to a log table and update it based on loading status.
I need to Create a ETL process to generate fact data by resolving the lookup.I have to read the files from a ftp site and load the data to a local Postgresql database with following features.
Before loading the data into database, log the list of files in log table and update its status (Downloaded , Processed , Failed)
· Any duplicate file should be notified via email.(no duplicate files to be loaded)
· Process can run only if all files are available.
· Notification of success and failure of job along with Job log.
· Propose a quality report for daily data load.
Expecting a help.
Thanks and Regards,
Naksh.

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Tue, 16 Dec 2014 11:20:04 GMT

Hi, I did the first step of reading files from ftp server.
tftpconnection-->tftplist-->tftpget

The first two components worked successfully,establishing a connection to the ftp server and reading files(.zip files are present in ftp server ). But when i try to get the file names using tftpget,its throwing null point error.
Since its zip files i gave filemask as "*." in tFileftplist. Is it correct?
What should be the extension to read a .zip file from remote server?

Thanks and Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Wed, 17 Dec 2014 08:28:24 GMT

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Wed, 17 Dec 2014 08:40:15 GMT

Hi,
The ftpserver which try to connect is ftp://ftp.gbi-len.com/. And when i go to this server,there is a directory named as 'incoming/'.
It has .zip files. I want to read these files from this incoming/ directory. Cana ny 1 please help me, what should be the filemask and what should I provide in the remote directory path:
In my job as of now i provided:
Remote Directory: "/incoming/"
Filemask: "*."
But when i run the job, iam getting error: Exception in component tFTPGet_1
java.lang.NullPointerException
Somebody please suggest on this.
Thanks and Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Wed, 17 Dec 2014 09:27:43 GMT

Hi,
Did you put global variable((String)globalMap.get("tFTPFileList_1_CURRENT_FILEPATH")) into Remote directory of tFtpGet?
Could you please show us your job setting screenshot?
Best regards
Sabrina

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Wed, 17 Dec 2014 10:13:16 GMT

Hi Sabrina,
Thanks for your response.
Yes i had provided the filepath variable .The issue which i was facing is instead of iterate, by mistake i have chose row connection as component ok. Thats why the null pointer error was showing.When i changed it to iterate,null pointer issue got resolved.
But now I am facing another issue. The job had run succesfully without any errors. It iterated 12 files from remote server and job ended without any errors. But the files did not get copied to my local directory. The destination is still empty.
I am attaching the job screen shot.
Many Thanks,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Wed, 17 Dec 2014 10:22:12 GMT

Hi naksh,
You should put global variable((String)globalMap.get("tFTPFileList_1_CURRENT_FILEPATH")) into Remote directory not file mask entry.
Please see my screenshot. Feel free to let me know if it is OK with you.
Best regards
Sabrina

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Wed, 17 Dec 2014 10:39:36 GMT

Hi Sabrina,
Thanks for your quick response.
I provided global variable correctly now. But now throwing error: Exception in component tFTPGet_1
com.enterprisedt.net.ftp.FTPException: 550 Failed to change directory.
Thanks and Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Wed, 17 Dec 2014 10:42:55 GMT

Hi,
When i directly login to ftp server with the provided username and password,I am able to view the files and able to download the files from there. Hence i hope this 550 it cannot be any permission issue.
Thanks and Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Thu, 18 Dec 2014 07:14:30 GMT

Hi naksh,
Could you share your current job setting with us?
Best regards
Sabrina

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Thu, 18 Dec 2014 09:26:39 GMT

Hi Sabrina,
Please find the job settings.
Thanks and Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Thu, 18 Dec 2014 09:45:53 GMT

Hi,

Yes i had provided the filepath variable .The issue which i was facing is instead of iterate, by mistake i have chose row connection as component ok. Thats why the null pointer error was showing.When i changed it to iterate,null pointer issue got resolved.
But now I am facing another issue. The job had run succesfully without any errors. It iterated 12 files from remote server and job ended without any errors. But the files did not get copied to my local directory. The destination is still empty.

When you put global variable((String)globalMap.get("tFTPFileList_1_CURRENT_FILEPATH")) in file mask entry of tFtpGet, no file is saved in your local?
Have you checked out "Die on error" option in tFTPGet to see if there is any error info printed on console?
Best regards
Sabrina

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Thu, 18 Dec 2014 10:22:20 GMT

Hi Sabrina,
I have checked out Die on error option also.No file is saving in my local. Instead throwing
' Exception in component tFTPGet_1
com.enterprisedt.net.ftp.FTPException: 550 Failed to change directory'
Thanks and Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Fri, 19 Dec 2014 10:15:02 GMT

Hi,
For testing, could you please try to deactivate all of components excepts tFTPGet(for log component) and create the FTP connection on the component itself, instead of using an existing connection, to see if it works.
Best regards
Sabrina

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Mon, 22 Dec 2014 07:40:07 GMT

Hi Sabrina,
I deactivated all components except tFTPGet and ran the job. Still its throwing error:
Exception in component tFTPGet_1
com.enterprisedt.net.ftp.FTPException: 550 Failed to change directory.
Thanks and Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Mon, 22 Dec 2014 08:19:23 GMT

Hi,
Could you please show us tFTPGet component setting screenshot?
Best regards
Sabrina

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Mon, 22 Dec 2014 11:00:42 GMT

Hi Sabrina,
Please find the tFTPGet settings

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Tue, 23 Dec 2014 07:15:14 GMT

Hi,
Could yo please use "/incoming/" instead of ((String)globalMap.get("tFTPFileList_1_CURRENT_FILEPATH")) in Remote directory? We have deactivated component tFTPFileList in your job design. There is only one component tFtpGet in your work flow.
Best regards
Sabrina

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Tue, 23 Dec 2014 09:44:17 GMT

Hi Sabrina,
Oh yes!! Careless mistake by me. Thanks for the reply. Now its downloading files to my local.
I activated all other components and in tFTPGet, i left the remote directory as "/incoming/" only
Now my job works fine.
Thanks Sabrina. U rocks!!
Regards,
Naksh

Re: [resolved] Read files from ftp site and load to postgresql db

Anonymous — Tue, 23 Dec 2014 09:51:18 GMT

Hi
That's really great to know that! Could you please mark this topic as resolved so that other community users will be informed that this thread has been resolved?
Best regards
Sabrina