Pentaho

 View Only

 How to loop through emails and include individual attachments

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Gert Wieland's profile image
Gert Wieland posted 04-08-2020 18:39

Hi Community,

 

I have a very urgent need at work. Usually I'd spend more time trying to figure it out, but right now I'd really appreciate some urgent help.

 

My scenario is fairly simple (I believe).

 

I have an Excel file with hundreds of lines. One or more lines belong to one person. I can also create a list of persons with their email addresses.

I want to send every person an email and attach only the part of the Excel file that belongs to them.

 

This is the (verbal) workflow I have in mind:

 

Go the first person and get their ID

From the Excel file, extract only the corresponding lines and create a new file (e. g. attachment.xlsx)

Send an email, inlcude attachment.xlsx

Delete attachment.xlsx

Then repeat for the next person

 

I'm stuck at "looping through the list of persons".

 

Could someone help me out here, please?

 

Thanks so much!

 

Gert

 

 

 

 

 

 

 

 


#Kettle
#Pentaho
#PentahoDataIntegrationPDI
Gert Wieland's profile image
Gert Wieland

I guess I'm about to figure it out... It will require a "job executor" to execute a job once for each line of a list that feeds into it. And it needs a data grid to configure the variables inside the transformation that's being run by the executor.

 

Will post a final solution here once I get there.

 

Gert Wieland's profile image
Gert Wieland

My biggest problem was that I got confused between "Parameters" and "Variables". I'm still not sure if I understood it 100 percent, but I got it to work, and it works very well. Below screenshot shows the steps.

 

In a nutshell:

1) You need a transformation that creates the list through which you want to iterate. The last step is a "Job Executor". The Job Executor will run a job for each entry in the list. You need to define the values (= Variable/ Parameter) you want to pass to the subsequent jobs and transformations.

 

2) Then you need to define the job the job operator should run. In my case, this job contains two transformations, "Prepare File" and "Send Email". You don't need to define the parameters here again.

 

3) Then, in each transformation, use the "Get Variables" step. The "Get Variables" button likely won't show them, but you can just type them in manually. In my example, which each iteration I'm sending a different email address, which is stored in "Parameter_Email".

 

That's all, I was able to send about 200 emails in less than two minutes.

 

Just posting it here in case somebody else finds this helpful.

 

 

SendBulkEmail