Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Tuesday, 17 February 2015

Pentaho Data Integration : Basics-2 : Sending an Email from Pentaho Kettle ETL (Attach Files, Send Log Files to email )

Kettle : Basics-2 : Sending an Email from Pentaho Kettle ETL (Attach Files, Send Log Files to email )


In this post we will see the basics of sending an email with attached files and logging files using Kettle ETL tool.
Version : 5.2.0 CE & this is tutorial is written for beginners. 

 Problem Statement : 

Send the below shown files to email along with log file.
Target Email : gmail account  (username : test4mail4kettle@gmail.com and password : Takeitall )

*You can access this account for testing purpose, kept open



 Sample Output:



Download Example transformation & Job : Click Me..!!

* Change your directory & the files


Step by Step procedure :
Design transformation for data flow -> i.e., file(s) information in the form of  rows.


 Design job for high level flow control:  sending mails on failure, sending files to mail and etc.


Transformation Design & how the flow of rows will be with steps
1) Drag and drop "Get File Names" step from "Input" category.
2) Browse for the folder location where you kept the files & click on "Add" button . Use .* [dot with star] wildcard to get all the files.

4) Preview the step 
  

5) 
* Drag & drop "Filter rows" step from "Flow" category & give below condition to get what type of files you want to pick from the folder ( folder may contain different types files. for ex : .txt, .docx , .csv and etc. 

* Make hop connection from "Get File Inputs" to "Filter rows"  

Condition to fetch txt file (text type files) from the folder (In this example I kept only txt files but there could be another type of files also in the folder)


 
 6) Now, drag & drop "Set files in result" step from "Job" category and give below shown settings

   
Filename field = filename
Type of file to = General

7) Now save the transformation & and have a preview. ( You will not find any result).

 Job Design & how the high level flow control is stetted with job entries 
1) Drag & release the "Start" job entry on to the canvas.
2) Drag & drop transformation entry & locate the transformation file just now created.
3) Connect "start" & "transformation entries".
4) Take a "Mail validator" entry from "Mail" category connect "transformation entry to it.
5) Inside "Mail validator" entry provide the email - Whether you are giving the correct format or not - it will check.
6) Now, take "Mail" entry from "Mail" category and connect "Mail validator" to this entry.
7) Open "Mail" entry settings by double clicking it.
8) You will find 4 tabs namely "Address", "Server", "Email Message", "Attached files".
Now, we will be setting properties in all the tabs which ever required for our purpose.

Address tab : 

Provide Target email address details(Destination) & sender email details with Name.

* Target email in this example is :  test4mail4kettle@gmail.com
* If you want to sent to multiple emails use space to add more emails.
* Sender address in this example is : test4mail4kettle@gmail.com
* Sender name is : Test Sender

Server Tab :

Gmail SMPT details information for google account : click here

SMTP Server
SMTP Server : smtp.gmail.com
Port : 465

Authentication
Authentication user : test4mail4kettle@gmail.com
password : Takeitall
 Seucure connection type : SSL ( check the secure connection)

Email Message Tab

 Play with various option in this settings

Attached Files tab

9) Save the job &run it.


Additional Notes :
Enable Log information as a file 
"attached files tab"  -> Select file type -> Multiselect General & log.
* Save the transformation & run it. In the destination mail along with files you can find log file information. 

With gmail:
You need to give less secure for your gmail .. It'll block the connection.

Resolution for gmail :

If this was you
You can switch to an app made by Google such as Gmail to access your account (recommended) or change your settings at https://www.google.com/settings/security/lesssecureapps so that your account is no longer protected by modern security standards.

Click on the link and set "Turn on"  for Access for less secure apps

References:


1) Pentaho Cook book 4
2) http://stackoverflow.com/questions/19302855/send-email-with-pentaho-pdi


Improvements & suggestions are most welcome on this post :-) Post your comments to improve....

Thank you for reading it. 

Sadakar Pochampalli - Business Intelligence
Hyderabad, INDIA