Send email with dynamic recipients & contents on Job completion

[former_member346203]

Hello,

  Here is the scenario - The DS job is writing data from a SQL table containing task lists to a csv file on a daily basis. Every day new tasks are added to the data. Upon job completion, DS needs to send out an email notification dynamically to all the assignees with the notification of the new tasks assigned to them. For ex. 3 new tasks are assigned to A and 2 to B, then on completion of the job each should get an email notification with the task numbers assigned to them - in this case 3 new nos. to A and 2 new nos. to B.

Please guide me towards the best way to accomplish this via DS

[Former Member · Accepted solution]

what is the source table meta data.

Can you please provide some more info about table then it would be more helpful

[former_member346203 · Accepted solution]

Hi Swapnil

   Table is being sourced from MS SQL Server 2008 R2.

Table metadata:-

TaskID (int, Primary key) - Unique ID for the task

Task_Details (Varchar) - Task Description

Assigned_to (Varchar) - Name of the Assignee

Email (Varchar) - Email of the Assignee

[former_member198401 · Accepted solution]

HI Abhishek,

Please refer to document

http://scn.sap.com/docs/DOC-60700

What you need to do is to

1) Create an output excel file(Target) using query transform which will contains the Task ID and Task Description and the email recipients.

2) Once Excel file is present in the accessible location, execute the script mentioned in the above document which will email the Task file to the desired recipients. Use a conditional work flow to route the File to different email recipients or you can send a single consolidated excel file to recipients.

Hope this helps

Regards

Arun Sasi

[former_member346203 · Accepted solution]

Thanks Arun ! Routing the message through an excel file seems to be good alternative, but I was wondering if there is way to have the task details in the body of the email.

Also, can you elaborate a bit on the Conditional Workflow ? The email to each recipient needs to consist only the tasks assigned to them.

[former_member198401 · Accepted solution]

HI Abhishek,

We can use Case transform to simplify our logic.

The data flow will look like below

Case logic

Data Flow:

You can use Excel Workbook as your format or Flat File format as your target

Regards

Arun Sasi

[former_member346203 · Accepted solution]

Thanks Arun !

      The CASE transform is perfect when we have a few number of recipients and the list is static. In our source table we can have hundreds of distinct recipients and new recipients get added everyday. We need the case condition to be dynamic,

[Former Member · Accepted solution]

Hi Abhishek,

you can store all the newly assigned task and recipients in separate table with sequence number which I think you can do with the help of table comparison.

Now after getting that list you can create three global variables

like

V_1 = get total no of rows in that table

Now put the conditional with exit condition like V_1 <= 0

Now while running in loop

create variable inside loop

V_2 = get receipt name where number = V_1

V_3 = get receipt role where number = V_1

Now you can put down email fields as shown in example by arun with different values generated by llop.

I hope you get my logic which can help you.

Thanks,

Swapnil

[former_member346203 · Accepted solution]

Thank you Swapnil & Arun ! I was able to generate the required output in an excel file and send them as attachments using your inputs.

[former_member198401 · Accepted solution]

Excellent!!!