Dynamic email body & send with Notification Activity

dylsing · Post by **dylsing** » Mon Jul 03, 2006 4:29 am

Hi,

I need to create a job where I make up an email body based on data from 2 different SQL extractions (but from the same source) I receive.

Examples of each piece of data I am extracting:

Data1
Module | Message | Source
-------------------------------------
France | Success | File1
UK | Success | File2

Data2
Module | Message | Reason | Source
----------------------------------------------------------
Spain | Fail | Missing number | File3
Germany| Fail | Missing name | File4

The final format I would like (based on the data above) in the email will be

Successful
---------------
France (File1)
UK (File2)

Fail
--------------
Spain - Missing number (file3)
Germany - Missing name (file4)

Can I actually do create an email body within DataStage like that and pass it to the DSSendEmail routine?

I have been using VBScript to do the above but now required to use DataStage to do it and I am clueless how this can be done. Any suggestion will be appreciated. Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Jul 03, 2006 6:25 am

Not without a routine somewhere in there, to build and send the email. The Notification Activity can not take an expression as the message body (though attachments are supported in version 7.5, so you could have the job produce the attachment file).

dylsing · Post by **dylsing** » Mon Jul 03, 2006 6:27 am

Just to add on, I have thought of saving the rows into a sequential file and use the Space character as a delimiter so that the contents in the sequential file will be:

France (File1)
UK (File2)

Can I use any of the stages to transfer all the contents of the sequential file (including the new line characters that separate each row) as a single string output into the email body of the Notification Activity or DSSendMail?

ray.wurlod · Post by **ray.wurlod** » Mon Jul 03, 2006 6:32 am

Not to the body. You could use a script to redirect stdin from that file. Or you could use a fixed body that says "see attachment".

dylsing · Post by **dylsing** » Mon Jul 03, 2006 6:32 am

ray.wurlod wrote:Not without a routine somewhere in there, to build and send the email. The Notification Activity can not take an expression as the message body (though attachments are supported in version 7.5, so yo ...

Just saw your email whilst I was posting. That's what I thought too, think I will have to explore just a bit more before giving this verdict to the client.

kommven · Post by **kommven** » Mon Jul 03, 2006 6:37 pm

If I were you, I will write what I need to a file (using a shell script maybe) and cat it in subject in "Notification Activity"

`cat mystatusFile.txt`

Try it it works for me...

Kommven-

kduke · Post by **kduke** » Mon Jul 03, 2006 7:47 pm

They are on Windows.

dylsing · Post by **dylsing** » Mon Jul 03, 2006 7:57 pm

kommven wrote:If I were you, I will write what I need to a file (using a shell script maybe) and cat it in subject in "Notification Activity"

`cat mystatusFile.txt`

Try it it works for me...

Kommven-

So when you use "cat" in shell script, it will return a concatenated value of the contents of the text file to your "Notification Activity"?

DSguru2B · Post by **DSguru2B** » Mon Jul 03, 2006 8:42 pm

'cat' will only display the contents of the file. If the file has the values concatenated together then yes it will, but thats in the unix world. Your on windows. I suggest coding all this in a basic routine. You code everything to get it in a way you want and then call the DSSendMail() function. Read about its format in DS help.

xcb · Post by **xcb** » Mon Jul 03, 2006 11:52 pm

In Windows the 'TYPE' command behaves the same as 'cat' in unix

Code: Select all

C:\>help type
Displays the contents of a text file or files.

TYPE [drive:][path]filename