topic Re: Loading .CSV file from SFTP Server to SQL Server in Talend Studio

Loading .CSV file from SFTP Server to SQL Server

Anonymous — Sat, 16 Nov 2024 03:26:51 GMT

Hi everyone,

I'm a new bee to Talend. I'm asked to do a POC on Talend. Please help me with your suggestions.

The requirement follows like below.

Once the .CSV files(multiple files)are detected in SFTP Server, A control file is created as a file event file event for a polling mechanism in Talend. A Talend job, once triggered by the polling event, compresses the exported data, transports them to another S-FTP CN file loaction. From this new SFTP location,I have to use Talend to load the data to SQL server database.If successful it moves csv-files to a backup share and deletes the control.csv file from the old SFTP Loaction.

Now I need to prepare a Design document for this whole process.

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Sun, 02 Feb 2020 18:31:45 GMT

If you know the flow you want, start building it. It's the best way to learn. If you have any issues while building it, come back with the issue you are suffering from and we can help. I'm afraid you are unlikely to find anyone on here with the time to talk you through the whole process from this description. But you will find plenty of people who will happily help you with sections of this.

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Mon, 03 Feb 2020 06:43:32 GMT

If you are using TAC and you are able to access the SFTP server folder, schedule a task on Job conductor and add a file trigger to trigger the task execution once a .csv file is created in the SFTP server folder.

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Thu, 06 Feb 2020 11:19:37 GMT

Hi Rhall,

Thanks for the response.

Yeah, You are correct. I have the plan and I'm already implementing it. I will come back to you If I get struck anywhere in the middle.

Thank you

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Sun, 09 Feb 2020 09:43:37 GMT

Hi Shong,

Could you please elaborate in detail.

Actually what I'm trying to do here is:

I have wrote a linux script which can detect the control file in a folder and moving the files actual file to be processed) to another remote server. Now I have to execute this linux script from talend, for this I'm using tssh component. But I'm not sure how to configure my server details like host name, port and other settings.

Can you please help me with your inputs.

Thanks a lot for the response.

Best regards,

Reddaiah Pidugu

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Mon, 10 Feb 2020 04:42:19 GMT

I have a sql query that is being pulled from a variable. Using this variable I want to create a .csv and save it on SFTP.

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Mon, 10 Feb 2020 06:20:12 GMT

Hi Andrew,

I don't understand what do you mean. can you please elaborate.

Thanks for the prompt response.

Best Regards,

Reddaiah Pidugu

Ph:+91-9177674469

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Tue, 11 Feb 2020 14:05:11 GMT

Hi Shong,

I got struck here in the implementation. Please help me.

I have a linux script that moves files from another server another remote server. using tSSH component i could call this script and executed. Now the files are in New SFTP server(linux). Now I have to extract those files and load them to SQL server tables.

But I'm not sure through which Talend component I can extract the files. where should i give the source files path and all.

My TOS is running on Windows. and I'm using AWS EC2 linux instances.

Please guide me. Thank you so much in advance!

Best Regards,

Reddaiah Pidugu

Ph:+91-9177674469

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Wed, 12 Feb 2020 05:51:45 GMT

@redtalend, you can't read the file diectly on SFTP server, you need to download the file to local machine where the job is executed, and then extract data from the file and load them to SQL server tables. To get the files from SFTP server, use tFTPGet component.

Regards
Shong

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Wed, 12 Feb 2020 06:56:57 GMT

Hi Shong,

Thanks for the response.

I hope I could explain you my requirement exactly.

You mean to say that we can't read the data directly from the files which resides in remote server (Linux box)?

Do I have to download them to my local machine and I have to configure the tFileInputDelimited component with the local machine path?

Kindly confirm.

Best Regards,

Reddaiah Pidugu

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Wed, 12 Feb 2020 07:04:41 GMT

Where will the job be execute? remote server (Linux box) or local machine? if the files are on SFTP server and you can only access the files through FTP protocol, the answer is yes, you have to get the files to local machine where the job is executed, and read the file using the tFileInputDelimited component.

Regards
Shong

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Wed, 12 Feb 2020 07:16:01 GMT

The Job gets executed on Local machine since TOS is on my local machine.

I didn't download/install TOS on Linux Box.

Can we access files from Linux box If we install TOS on Linux box?

Thanks & Best Regards,

Reddaiah Pidugu

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Thu, 13 Feb 2020 06:17:04 GMT

Take a look at the tFTPGet component and learn how to get the files to from SFTP server to local machine.

Regards
Shong

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Mon, 17 Feb 2020 09:07:44 GMT

Thank you so much Shong for the solution.

I could solve the issue.

Best Regards,

Reddaiah Pidugu

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Mon, 17 Feb 2020 09:12:57 GMT

Great, thanks for your feedback!

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Wed, 19 Feb 2020 06:59:59 GMT

Hi Shong,

I'm facing an issue with my job design.

My job has to pick 3 files(same structure) from a local machine and load into SQL server.

But it is picking all the files and loading only one file data to SQL server.

Please find the attachment my job design and suggest me where I'm doing wrong.

Thanks & Regards,

Reddaiah Pidugu

TalendJobDesign.PNG

Re: Loading .CSV file from SFTP Server to SQL Server

Anonymous — Wed, 19 Feb 2020 07:13:43 GMT

Shong, Also please note that all the files are in same folder and today I have 3 files and tomorrow it may vary.

Thanks,