DSXchange

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

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).

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?

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".

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.

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-

They are on Windows.

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"?

'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.

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