Enriching your work by sending an email

When you work with ETL processes, sending emails is a very common requirement. In this section, you will learn how to send emails with PDI by enriching your previous work. In this case, the email will replace the log message.

For trying this exercise, you need access to an SMTP server. You also need at least one valid account to play with. The example uses Gmail's SMTP server (smtp.gmail.com), but you can use any email server, as long as you have access to the information required by PDI. 

Before starting to work with Spoon, let's introduce the kettle.properties file. In this file, you define variables with the JVM scope. We will use it for storing the email settings:

1. Open the kettle.properties file located in a folder named .kettle inside your home directory. If you work under Windows, that folder could be C:\Documents
   and Settings\<your_name> or C:\Users\<your_name>, depending on
   the Windows version. If you work under Linux (or similar) or Mac OS, the folder will most probably be /home/<your_name>/.

Note that the .kettle folder is a system folder, and as such, may not display using the GUI file explorer on any OS. You can change the UI settings to display the folder, or use a terminal window.

1. Add the following lines, replacing the values between <> with valid information, that is, with your email and password. You may also change the values for the SMTP server as well. Then save the file as follows:

SMTP_SERVER=smtp.gmail.com
SMTP_PORT=465
AUTH_USER=<your email>
AUTH_PASSWORD=<your password>

Now we will modify the Job:

1. Restart Spoon so it can recognize the changes in the kettle.properties file.
2. Open the Job created in the previous exercise, and save it with a new name.
3. Delete the Write To Log entry and replace it with a Mail entry from the Mail category. This is how your Job will look:
   
   

Job that sends an email

1. Double-click the Mail entry. Under the Addresses tab, fill the Destination address textbox with a valid email address. In addition, you need to complete the Sender address and the Sender name textboxes.

You can specify more than one destination account, using a space as a separator in the destination address of the Mail Job entry.

1. Complete the Server tab as shown in the following screenshot:

Configuring the server in a mail Job entry

The password is not visible, but it has a value: ${AUTH_PASSWORD}. You can enter the value by hand or by pressing Ctrl + space and picking the value for the list of available variables.

1. Complete the Email Message tab, as shown next. Don't forget to check the Only send comment in the mail body? option:

Filling the email message tab

1. Save the Job and press F9 to run it.
2. Click on the Variables tab as you did before. This time you will see the list of all used variables, along with their default values set in kettle.properties. Among these variables, there will be one variable without a default value, MY_FOLDER. Provide a value, a name for the folder to be created, and click on Run. Supposing that the folder didn't exist, the folder will be created and the Mail entry will not be executed.
3. Repeat the previous step. As the value for MY_FOLDER, type exactly the same folder name as before. This time, the creation of the folder will fail and a mail will be sent to the destination.

As you could see, sending emails is a simple task. If you explore Spoon, you will discover that there is also a Transformation step for sending emails. In general, you could use either the step or the Job entry with similar results, but there are particular scenarios where one approach is better than the other, or where only one of the methods applies. Here you have some examples:
If your Pentaho Job failed and you want to report the status by email to an administrator, you will do it in a Job.
If you want to send several emails, with different content and to different recipients (for example, a custom welcome letter to a list of subscribers to a course), you will prefer a Transformation.
For full details about how to configure the entry, you can visit http://wiki.pentaho.com/display/EAI/Mail.