Jeff's Cluttered Mind

Informatica allows options for sending email at the session level (on Session Success or Failure) but there is not a standard way to send email from the mapping. By using a web service (written in a third party language such as .Net or Java) to send email, you can generate email from a mapping based on conditional logic and include relevant "pipeline" data in the email body. An additional advantage, if you are using HTML email, you have unlimited options to format the body of the email.

Informatica mappings can consume web services from third party applications, treating them as Source objects, Targets or Transformations. The example code for this article will aggregate data into an HTML table and use a web service Target to send it via email.

Since there are many ways to do the same thing, I will not elaborate on how to create the email web service. If (like me) this is not your area of expertise, consult a .Net or Java developer and ask them to code a simple web service that will send an email based on these input parameters: from email id, to email id, subject line, email body.

The Example Mapping

He’s my example scenario that requires Informatica to send conditional email:

Send an email containing a list of materials, their description and plant location, embedded in the body of the email, to a group associated with the material. The mapping's source table will contain multiple groups and all applicable product information, each group should receive one only email, listing all their products.

This image shows how the email should look:

Download the Example

An example mapping can be downloaded here, along with the session, workflow, source data file and parameter file. I could not include the web service “Target” in the download so I used a flat file as the Target. You can replace the flat file with your web service.

If you want to run the mapping with only the flat file target, you can get an idea of how the output would look by pasting the output into an HTML demo site.

After downloading and importing the examples, you will want to put the source file “Infa_eMail.csv” in the standard Informatica Source folder and put the emailTest.txt parameter file in the standard Informatica Target folder. The flat file target will output to the Target folder.

How It Works

The main work in the mapping is done by the exp_Build_HTML_Table mapping transformation. It preforms the following task:

• It builds an HTML table row for each group’s material data
• It evaluates current and previous group id, if the group id is the same, the HTML table row is appended to the previous table row. If the group is different (the source data is sorted by group) a new table row section is started
• A Source’s row counter is incremented, the counter is used to control the background color on alternating table rows. The counter is initialized to 1 when a new group is detected.

The Aggregator transformation preforms the task of “grouping” the materials so there is only one email sent per Group Id. After the aggregation, table rows are concatenated with the HTML code for Table header and footer any additional email body text and sent to the target i.e. the web service to physically send the email.

Conclusion

A email web service is your best option for enhancing Informatica’s base functionality. I hope you can use this basic example to get started. Use the comment section to leave feedback on this technique or suggest alternatives.

The email body styles were gleaned from this article on CSS.

Thank you for your support

or