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

One Star

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

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.

Accepted Solutions
One Star

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

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!!Smiley Happy
Regards,
Naksh

All Replies
One Star

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

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
One Star

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

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
One Star

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

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
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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
Moderator

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

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


--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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
One Star

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

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
Moderator

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

Hi naksh,
Could you share your current job setting with us?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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
Moderator

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

Hi,
Could you please show us tFTPGet component setting screenshot?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

Hi Sabrina,
Please find the tFTPGet settings
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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!!Smiley Happy
Regards,
Naksh
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.