Saturday, 24 August 2013 10:37

FTPES PUT solved with Talend Featured

Written by 
Rate this item
(1 Vote)

We were trying to automate FTP to our cpanel folders. As we are using Talend for all our jobs it was sensible to do the same from Talend only to keep the entire build process sequential through a single job. Due to Talend limitations of not allowing FTPES put we tried searching for a workaround and finally we cracked the same using WinSCP.

Please go through the below process to make it work for your system as well.

Prepare environment – application and folders
Here is folder structure and script files I created on Talend server (to which files were transferred):
C:\app\WinSCP – folder contains binary files of portable WinSCP application
C:\Data\Scripts – folder contains batch script to run FTP transfer and script with commands for FTP
C:\FTP – transfer folder to which files will be transferred from remote site
C:\Data\Scripts\ftprun.cmd – batch file with command which starts WinSCP with certain parameters
C:\Data\Scripts\ftpscript.txt – text file which contains commands for WinSCP. It is passed to WinSCP as a parameter by ftprun.cmd script

Working with WinSCP:

Download portable version of WinSCP (http://winscp.net/eng/download.php) and unzip toC:\Apps\WinSCP folder.

Create connection profile in WinSCP.

First we need to define connection profile in WinSCP. That connection profile will be used later in ftpscript.txt to establish connection with FTP server.

In order to define connection profile run winscp.exe from C:\Apps\WinSCP folder and populate WinSCP Logon window with all details about connection (including password).

Once all details are filled in click Save…

In Save session as… window name your session (that name will be used later on in ftpscript.txt to open connection) and make sure that Save password (not recommended) checkbox is ticked. We want to remember password in connection profile (WinSCP stores passwords in encrypted form). Otherwise we will have to entre clear-text password in ftpscript.txt file.

Once connection profile has been saved in screen will show it on the list in WinSCP Login window.

To make sure that all details are correct highlight connection you just created and click Login button.
After that WinSCP will attempt to establish connection using parameters just delivered.

 If connection is initiated for the first time and FTP server has been defined as FTP with TLS then we can expect that WinSCP might request acceptance for certificate presented by the server. In that case Warning windows will show up with all details of the certificate (as on screenshot below – you can see default, system-generated certificate which has been assigned to FTP site on IIS). Accept certificate by clicking Yes, so WinSCP can store information about certificate in configuration file for future use.

NOTE: It is important to establish connection to FTP server with TLS at least once before transfer task will be scheduled. That way we can accept and save information about certificate, so WinSCP will not be asking about that later.

ftpes 4

Once certificate is accepted and other connection details are fine we should see two panels with files. One panel shows files on our computer, second panel shows files on remote FTP server (in that case there is only one file Text file.txt). That indicates that we can establish connection successfully and all parameters are entered correctly to connection profile.

ftpes 5

As we have connection profile created and saved in WinSCP configuration we can proceed to next step in which we will create some scripts as preparation to automated transfers.
Prepare script to run FTP transfer and script with commands for FTP
In here we will create two scripts which will help to automate file transfer with WinSCP.
ftpscript.cmd – batch file responsible for starting WinSCP command with appropriate parameters
ftpscript.txt – list of commands for WinSCP to execute
NOTE: Both files will be placed in C:\Data\Scripts folder.

ftpscript.cmd

C:\app\WinSCP\winscp.com /script=C:\app\Data\Scripts\ftpscript.txt

ftpscript.txt

option batch continue
option confirm off
open [yourhostname]
lcd [folder from which files to be uploaded]
synchronize remote -delete
synchronize remote [folder from which files to be uploaded] [folder to which files to be uploaded]
exit

Example as below:

option batch continue
option confirm off
open yourhostname.in
lcd C:\togglebox\Images\FINALREPORT
synchronize remote -delete
synchronize remote C:\togglebox\Images\FINALREPORT /public_html/phocadownload
exit

NOTE: In ftpscript.txt file line open osintegration.in indicated connection profile name which will be used to establish connection.
Before scheduling script it is worth to check how it works and we get expected results, so from Command Prompt you can just run ftpscript.cmd and see how it behaves and if everything is transferred according to the plan.
Here is example output of ftpscript.cmd command started before scheduling:

batch continue
confirm off
Connecting to osintegration.in ...
Connected with osintegration.in, negotiating SSL connection...
SSL connection established. Waiting for welcome message...
Connected
Starting the session...
Reading remote directory...
Session started.
Active session: [1] - This email address is being protected from spambots. You need JavaScript enabled to view it.
C:\togglebox\Images\FINALREPORT
Comparing...
Local 'C:\togglebox\Images\FINALREPORT' => Remote '/public_html/phocadownload'
Synchronizing...
Local 'C:\togglebox\Images\FINALREPORT' => Remote '/public_html/phocadownload'
C:\togglebox\Images\FINALREPORT\BSE_SENSEX_REPORT_COMPLETE.pdf | 1964 KiB | 18.2 KiB/s | binary | 100%
C:\togglebox\Images\FINALREPORT\bse30days.zip | 876 KiB | 26.9 KiB/s | binary | 100%
C:\togglebox\Images\FINALREPORT\bsetodays.zip | 0 KiB | 24.8 KiB/s | binary | 100%
C:\togglebox\Images\FINALREPORT\NSE_SENSEX_REPORT_COMPLETE.pdf | 1584 KiB | 20.1 KiB/s | binary | 100%
C:\togglebox\Images\FINALREPORT\nse30days.zip | 625 KiB | 18.1 KiB/s | binary | 100%
C:\togglebox\Images\FINALREPORT\nsetoday.zip | 0 KiB | 16.0 KiB/s | binary | 100%
Comparing...
Local 'C:\togglebox\Images\FINALREPORT' => Remote '/public_html/phocadownload'
Nothing to synchronize.

Once everything went well we ready to create scheduled task to trigger file transfer automatically. If test connection failed for some reason it needs troubleshooting then.

Working with tSystem Talend component:

tSystem 1

Once the above procedure completes successfully then the below component needs to be added to the Talend job to complete the task:

Configuring tSystem component:

tSystem 2

Save the job and execute from Talend designer.

Reference links: http://blog.skufel.net/2011/12/automating-file-transfer-via-sftp-i-ftps-using-winscp/

Read 5570 times Last modified on Saturday, 24 August 2013 11:19
osiauthor

This is the author profile created for few selected user by admin.

More in this category: « Implementing Open Source BI

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.