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/

Published in Technical Blogs

Talend tSendmail tutorial


The Below tutorial on tSendmail expalins how to send a mail with attachments from talend ETL job. The tutorial further explains the concept of using a job level context variable usage for creating a file attachment variable.

Refer below for step by step implementation of file attachment:

0

 

Configuring the general mail option:

1

Set the mailing list and mail features as below for fields:

To, From, Sendername, Cc, Bcc, Subject and Message.

Please Note: Attachments is explained at the end.

2

Configure the mail host as per the mail host services used.

SMTP Host and Port

Use SSL Support and STARTTLS Support as per the service provider configuration. 

 

Getting attachment to Talend tSendmail using a context variable as below:

Create Context variable:

3

 

 

 

 

 

4

Click Next

5

Enter the Name in below screen and click on Value as Tree tab:

6

Click on tSendmail_example

Enter the file path to the value. Please note for windows users the backslash “/” needs to be used.

7

Click Finish.

Import the context variable to the job:

Click on context sign as below:

8

Select the newly created variable and click ok

9

Click ok.

10

Include the context file in the tSendmail component in attachment as below:

Click on + sign as below.

11

 

Select the file name and press ctrl+space and the below screen appears:

12

13

Double click on the added context variable i.e. context.tSendmail_example.

14

By Using the above variable you can attach the file referred by the context variable to the sent mail.

Published in Technical Insight

Integrating Jasper Report in Talend Open Studio(tJasperOutput)


Use the tJasperOutput component to run a Jasper Report from Talend Open Studio.  This component allows a report to be produced after a data flow is executed.

tJasperOutput-1

Published in Technical Insight