Nowadays, it is very common to use e-mails to exchange digital messages from one author to several recipients. These e-mails can also have a list of files attached that will be sent along with the message.
Kettle offers job entries and transformation steps that allow the sending of e-mails with attached files. In this recipe, you will use the Email job entry to send an e-mail with a set of files attached with a particular condition: the files must have been modified after a given date.
You need a directory named filesToAttach containing sample files. You will also need access to an SMTP server. You can use the smtp.gmail.com server. You also need at least one valid account to play with.
Carry out the following steps:
- Create a new transformation.
- Drop a Get Files Names from the Input category.
- Under the File tab, type or browse for the
filesToAttachfolder in the File or directory textbox. Type.*in the Regular Expression textbox and click on the Add button to populate the Selected files grid. - Under the Filter tab, uncheck on the Add filename to result checkbox.
- Add a Filter rows step from the Flow category. Here, add a new condition, select the field
lastmodifiedtime, the operator', and enter a constant value date to compare with, for example,2010/11/01. Don't forget to select the Conversion format expression to match the date format. - Add a Set files in result step (Job category).
- Create a hop from the Filter rows step towards this step. When asked for the kind of hop, select Main output of step. The transformation will look similar to the one shown in the following diagram:
- Double-click on the Set file in result step. Select
filenamein the Filename field, andGeneralin the Type of file to prompt. - Create a new job, and drop a START job entry into the canvas.
- Add a Transformation job entry from the General category.
- Double-click on this job entry and type the complete path of the previously created transformation (or browse for it) in the Transformation filename textbox.
- Add a Mail validator job entry from the email category. In this step, type the destination account in the Email address textbox.
- Add a Mail job from the Mail category.
- Under the Addresses tab, fill in the Destination address textbox and the Sender address textbox with two valid e-mail addresses (they can be the same if you only have one account to play with). In addition, you need to complete the Sender name textbox. For example, you can type your name.
- Complete the Server tab as shown in the following screenshot:
Note
You need to fill the Authentication user and Authentication textboxes with a valid user and password. If your account is
[email protected], then you have to typeuserin the Authentication user textbox, and your e-mail password in the Authentication textbox.The default Port for the SMTP Server is
465, but if you use a secure connection through TLS, then the default is587. - Under the Email Message tab, complete the Subject textbox and the Comment area with sample text. Also, check the Only send comment in the mail body? checkbox.
- Select the Attached Files tab and check the option Attach file(s) to message?
- Select the option
Generalin the Select file type listbox. - Check the Zip files to single archive? checkbox. Type a filename in the Name of zip archive textbox (for example,
files.zip). - Running the job, an e-mail will be sent to the destination address (if it is valid). It will include an attached ZIP file with all of the files from the
filesToAttachfolder that fulfill the date condition.
The first task in the example was to create a transformation that gets the files that will be sent as an attachment with the e-mail. For this, you used the Get Files Names step to get the files inside the filesToAttach folder, and then the Filter rows step to filter the files with a lastmodifiedtime greater than a specific date, in this case 2010/11/01. Only the files that pass this condition will be set in the result. Remember to uncheck the Add filename to result checkbox in the Get Files Names step, because you don't want all the files, only the result after the row filtering.
The job run this transformation, and then executes the email job entry. This job is quite self-explanatory. Under the Addresses tab, you must complete the destination and sender e-mails addresses, and the e-mail content under the Email Message tab.
The example uses Gmail's SMTP server (smtp.gmail.com), but you can use any e-mail server as long as you have access to the information required in the Server tab. Take a look at the firewall configuration if you have problems reaching the specified address.
Under the Attached Files tab, you selected the file type option named General; this indicates that all the files included in the result list will be attached to the e-mail. In addition, you configured the entry to zip all files into a single file.
Note
If the previous jobs or transformations also added files to results, then those files will be attached as well. Therefore, a Delete filenames from result job entry might be required at the beginning of this job.
You used the Mail validator job entry to verify the e-mail address structure. If the structure is not correct, then the email job entry will not run. You could use the SMTP check? checkbox here, if you want to validate the existence of the accounts.
You will find more features about sending e-mails in the following subsections:
Let's assume that you want to include the log of the transformation inside the attached ZIP file. This is very simple; just double-click on the Transformation entry job and set the log file under the Logging settings tab, as shown in the following screenshot:
You can specify a more detailed level of log in the Loglevel listbox if you want.
Then, you need to include this file as an attached file. Double-click on the email job entry, select the Attached Files tab, and select the Log item in the Select file type listbox. As you need to select both General and Log items, you have to do it by pressing the Ctrl key.
After running the job, an e-mail will still be sent, but this time, the ZIP file attached will also contain a new file named logFile.log with the log information.
There is a transformation step named Mail, in the Utility category, which is very similar to the Mail job entry. The main difference is that the Mail step sends one e-mail for each existent row.
Its configuration has listboxes instead of textboxes to refer to different settings (addresses, server configuration, subject, body, and so on). Here you should refer to existing fields instead of typing them.
In some cases, 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. For example:
- If you need to send several e-mails to different destination addresses, and/or with different content, and/or with different attached files, then it is better to use the Mail transformation step.
- If you need to send an e-mail with some information about the executed process—for example, the time that it took to execute it—you must use the Mail job entry.
- If you need to send only one e-mail with attached files coming from different jobs or transformations, then you should use the Mail job entry.
For an advanced example of using the Mail step for mailing, you can follow this link: http://kjube.blogspot.com/2011/01/mailing-new-years-cards.html. The blog post by KJube explains a transformation that sends best wishes for the New Year to a list of people. The following two considerations arise about the example provided:
- The transformation uses the Excel Writer plugin step. If you are working with Kettle 4.1, then you should install the plugin in order to open the transformation. That's not the case for Kettle 4.2, in which the plugin is already included as an Experimental step.
- The values for the Server configuration and the Sender name and Sender address are stored in Kettle variables; they don't change from row to row. However, as explained earlier, you cannot type the values directly in the step configuration window. You should refer to existing fields instead. Therefore, it is necessary to get those variables in a previous step.