Now you will build a sales report and send it by e-mail. In order to follow the tutorial, you will need two simple prerequisites:
Once you've checked these prerequisites, you are ready to start.
today
. As Type, select Today 00:00:00
. js
—the name of the connection to the jigsaw puzzles database.SELECT pay_code , COUNT(*) quantity , SUM(inv_price) amount FROM invoices WHERE inv_date = ? GROUP BY pay_code
${LABSOUTPUT}/sales_
as Filename. transformations
folder you created in a previous tutorial, with the name sales_report.ktr
. ${Internal.Job.Filename.Directory}/transformations/sales_report.ktr
as the transformation filename, either by hand or by browsing the folder and selecting the file. [email protected]
, then as user enter pdi_account
and as password provide your e-mail password. Error generating sales report
.You generated an Excel file with a crosstab report of sales on a particular day. If the file is generated successfully, an e-mail is sent with the Excel file attached. If some error occurs, an e-mail reporting the problem is sent to the administrator.
If you skipped Chapter 8 and still know nothing about databases with PDI, don't miss this exercise. Instead of the proposed sales report, create a transformation that generates any Excel file. The contents of the sheet is not the key here. Just make sure you leave the Add filenames to result option checked in the Excel output configuration window. Then proceed as explained.
The execution of any job entry either succeeds or fails.
In particular, the job entries under the category Conditions just evaluates something and success or failure depends upon the result of the evaluation.
For example, the job entry File Exists succeeds if the file you put in its window exists. Otherwise, it fails.
Whichever the job entry, you can use the result of its execution to decide which of the entries following it execute and which don't.
In the tutorial, you included a transformation job entry. If the transformation runs without problem, this entry succeeds. Then the execution follows the green hop to the first Mail job entry.
If, while running the transformation, some error occurs, the transformation entry fails. Then the execution follows the red path toward the e-mail to the administrator.
So, when you create a job, you not only arrange the entries and hops according to the expected order of execution, you also specify under which condition each job entry runs.
You can define the conditions in the hops. The following table lists the possibilities:
Color of the hop |
What the color represents |
The interpretation |
---|---|---|
Black |
Unconditional execution |
The destination entry executes no matter the result of the previous entry. |
Green |
Execution upon success |
The destination entry executes only if the previous job entry is successful. |
Red |
Execution upon failure |
The destination entry executes only if the previous job entry failed. |
At any hop, you can define the condition under which the destination job entry will execute. By default, the first hop that leaves an entry is created green, whereas the second hop is created red. You can change the color, that is, the behavior of the hop. Just right-click on the hop, select Evaluation, and then the condition.
One exception is the hop or hops that leave the START step. You cannot edit them. The destination job entries execute unconditionally, that is, always.
Another exception is the special entry Dummy that does nothing, not even allowing you to decide if the job entries after it run or not. They always run.
Here we will modify the job that sends the e-mail containing the sales report.
${java.io.tmpdir}
. If there is no sale for today, don't generate the file. You do this by checking the Do not create file at start option in the Excel output step.In the tutorial you configured two Mail job entries. In the mail that follows the green hop, you attached the Excel file generated by the transformation. However, you didn't explicitly specify the name of the file to attach. How could PDI realize that you wanted to attach that file? it could because of the Add filenames to result checkbox in the Excel output configuration window. By checking that option, you added the name of the Excel file to a special list named File result.
When PDI hits an e-mail entry where Attach file(s) to message? is checked, it attaches to the e-mail all files in the File result list.
Most of the transformation steps that read or write files have this checkbox, and it is checked by default. The following sample belongs to a Text file input step:
Each time you use one of these steps you are adding names of files to this list, unless you uncheck the checkbox.
There are also several job entries in the File management and the File transfer categories that add one or more files to the File result list. Consider the following Copy Files... entry screen:
As with the Mail entry, there are some other entries that use the File result list. One example is Copy or Move result filenames. This entry copies or moves the files whose names are in this special list named File result.
3.133.122.68