Loading .CSV file from SFTP Server to SQL Server

[Anonymous]

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. 

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

@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

[Anonymous]

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