One of the advantages of using ETL Tools is that functionality such
as monitoring, logging and notification are either built-in or very easy
to incorporate into your ETL with minimal coding. This Post explains
the
Email task, which is part of Notification
framework in Informatica. I have added some guidelines at the end on a
few standard practices when using email tasks and the reasons behind
them.
1. Workflow and session details.
2. Creating the Email Task (Re-usable)
3. Adding Email task to sessions
4. Adding Email Task at the Workflow Level
5. Emails in the Parameter file (Better maintenance, Good design).
6. Standard (Good) Practices
7. Common issues/Questions
1. Workflow and session details.
Here is the sample workflow that I am using. The workflow (wkf_Test) has 2 sessions.
s_m_T1 : Loads data from Source to Staging table (T1).
s_m_T2 : Loads data from Staging (T1) to Target (T2).
The actual mappings are almost irrevant for this example, but we need
atleast two sessions to illustrate the different scenarios possible.
Test Workflow (2 Sessions)
2. Creating the Email Task (Re-usable)
Why re-usable?. Becuase we’d be using the same email task for all the sessions in this workflow.
1. Go to Workflow Manager and connect to the repository and the folder in which your workflow is present.
2. Go to the Workflow Designer Tab.
3. Click on Workflow > edit (from the Menu ) and create a workflow variable as below (to hold the failure email address).
Failure Email workflow variable
4. Go to the “Task Developer” Tab and click create from the menu.
5. Select “Email Task”, enter “Email_Wkf_Test_Failure” for the name
(since this email task is for different sessions in wkf_test).
Click “Create” and then “Done”. Save changes (Repository -> Save or the good old ctrl+S).
6. Double click on the Email Task and enter the following details in the properties tab.
Email User Name : $$FailureEmail (Replace the pre-populated session variable $PMFailureUser,
since we be setting this for each workflow as needed).
Email subject : Informatica workflow ** WKF_TEST ** failure notification.
Email text : (see below. Note that the server varibles might be disabled, but will be available during run time).
Please see the attched log for Details. Contact ETL_RUN_AND_SUPPORT@XYZ.COM for further information.
%g
Folder : %n
Workflow : wkf_test
Session : %s
Create_Email_Task
3. Adding Email task to sessions
7. Go to the Workflow Tab and double click on session s_m_T1. You should see the “edit task” window.
8. Make sure you have “Fail parent if this task fails” in the general tab and the “stop on errors” is 1 on the config tab.
Go to “Components” tab.
9. For the on-failure email section, select “reusable” for type and click the LOV on Value.
10. Select the email task that we just created (Email_Wkf_Test_Failure), and click OK.
Adding Email Task to a session
4. Adding Email Task at the Workflow Level
Workflow-level failure/suspension email.
If you are already implementing the failure email for each session
(and getting the session log for the failed session), then you should
consider just suspending the workflow. If you don’t need session level
details, using the workflow suspension email makes sense.
There are two settings you need to set for Failure notification emails at workflow level.
a) Suspend on error (Check)
b) Suspension email (Select the email task as before).
Again,
remember that if you have both session and workflow level emails,
you’ll get two emails, if a session fails and causes the parent to fail.
Informatica workflow suspension email
Workflow Sucesss email
In some cases, you might have a requirement to add a success email once the entire workflow is complete.
This helps people know the workflow status for the day without having to
access workflow monitor or asking run teams for the status each day.
This is particularly helpful for business teams who are more concerned
whether the process completed for the day.
1) Go to the workflow tab in workflow manager and click Task > Create > Email Task.
2) Enter the name of the email task and click OK.
3) In the general tab, select “Fail parent if this task fails”. In the properties tab, add the necessary details
Note that the variables are not available anymore, since they are only applicable at the session level.
4) Add the necessary Session.status=”succeedeed” for all the preceding tasks.
Here’s how your final workflow will look.
Informatica success emails
5. Emails in the Parameter file (Better maintenance, Good design).
We’ve created the workflow variable $$FailureEmail and used it in the email task. But how and when is the value assigned?
You can manage the failure emails by assigning the value in the
parameter file.
Here is my parameter file for this example.
You can seperate multiple emails using comma.
infa@ DEV /> cat wkf_test.param
[rchamarthi.WF:wkf_Test]
$$FailureEmail=rajesh@etl-developer.com
[rchamarthi.WF:wkf_Test.ST:s_m_T1]
$DBConnection_Target=RC_ORCL102
[rchamarthi.WF:wkf_Test.ST:s_m_T2]
$DBConnection_Target=RC_ORCL102
While it might look like a simpler approach initially, hard-coding emails IDs in the email task is a bad idea. Here’s why.
Like every other development cycle, Informatica ETLs go thorugh Dev,
QA and Prod and the failure email for each of the environment will be
different. When you promote components from Dev to QA and then to Prod,
everything from Mapping to Session to Workflow should be identical in
all environments. Anything that changes or might change should be
handled using parameter files (similar to env files in Unix). This also
works the other way around. When you copy a workflow from Production to
Development and try to make changes, the failure emails will not go to
business users or QA teams as the development parameter file only has
the developer email Ids.
If you use parameter files, here is how it would be set up in different environments once.
After the initial set up, you’ll hardly change it in QA and Prod and migrations will never screw this up.
In development : $$FailureEmail=developer1@xyz.com,developer2@xyz.com"
In QA / Testing : $$FailureEmail=r=developer1@xyz.com,developer2@xyz.com,QA_TEAM@xyz.com
In Production : $$FailureEmail=IT_OPERATIONS@xyz.com,ETL_RUN@xyz.com,BI_USERS@xyz.com
6. Standard (Good) Practices
These are some of the standard practices related to Email Tasks that I would recommend. The reasons have been explained above.
a) Reusable email task that is used by all sessions in the workflow.
b) Suspend on error set at the workflow level and failure email specified for each session.
c) Fail parent if this task fails (might not be applicable in 100% of the cases).
c) Workflow Success email (based on requirement).
d) Emails mentioned only in the parameter file. (No Hard-coding).
7. Common issues/Questions
Warning unused variable $$FailureEmail and/or No failure emails:
Make sure you use the double dollar sign, as all user-defined
variables should. (unless you are just using the integration service
variable $PMFailureEmailUser). Once that is done, the reason for the
above warning and/or no failure email could be…
a) You forgot to declare the workflow variable as described in step 3 above or
b) the workflow parameter file is not being read correctly. (wrong
path, no read permissions, invalid parameter file entry etc.)
Once you fix these two, you should be able to see the success and failure emails as expected.
