Including parameters/userstatus in email notification

[johnno]

Hi all,

I am trying to send an email notification (using a Notification Activity in a job sequencer) which includes a link to a report file. I though the best way to do this would be by using parameters or userstatus values to include in the email body, but this does not work.

Can anyone offer any ideas on how I might do this?

Cheers
Johnno

[Amos.Rosmarin]

Do you want to include the report in your mail or just create a dymanic hyperlink to the files location ?

I'm both cases I think it's better to use your own mail sender routine instead of the notification stage, you'll have much more flexibility.

Your email format is determined by the dssendmail_template.txt file in your project directory.


Amos

[chulett]

Just to clarify what Amos said...

You have no flexibility with the Notification stage. You cannot use job parameters anywhere in it, surprising as that sounds.

To do what you want to do, you'll need to write your own routines and/or scripts to interface with sendmail or the equivalent you are using on Windows.

[1stpoint]

There is a limitation in the sendmail interface in that it only supports one recipient (at a time).

I have created a standalone batch that accepts parameters (Subject, Message (or Messagefile), RecipientList) and execute that batch within either a Sequencer or controlling batch.

[chulett]

That's not true... at least on our platform, 6.x on HP/UX. I routinely send notifications to multiple recipients - they only need to be "white space" separated to work fine.

When we need to send to mupltiple people on a regular basis, we create email groups to simplify things.

[Amos.Rosmarin]

Windows is Windows and unix is unix and if they were the same we wouldn't be swearing so much .....


You can alway use another sendmail utility such as blat thjere you cann attach a file.

[johnno]

Thanks very much folks. Our intention (at least for the time being) is to send to a single recipient, so multiple addresses are not important yet.

I will try and track down some ideas on how to achieve this via routines - I think I can remember some old posts that mention this.

Thanks again

[1stpoint]

Per Ascential Support:

This is a known issue in DataStage. You are not able to put multiple
recipients in the mail notification stage.

The work around is use multiple mail notification stages in your job.

or

Send up an email alias on your mail server that is able to send to more
than one person.

Please let me know if you have any further questions.

Best,

Aaron Vandesteen
Ascential Client Support

[chulett]

I've seen the same quote from Support. Have you... tried it? Like I said, works fine for me, white-space delimited. YMMV, of course.

[1stpoint]

This may work on unix, but definitely not on Windows.
I ended up writing a SendMail batch that accepts a recipient list and
sends an email to everyone on the list (independently).

Code: Select all

**************************************************************************************************************************
* Batch:SendMail
* Sends mail to multiple recipients using the DataStage sendmail utility.
* Version 1.0
*************************************************************************************************************************
* Synopsis:
* The MessageFile parameter can be a FileName or a literal Text Message.
* If it's a valid (existing) filename, it's content will be sent as the message body.
* If it's a Text Message (non existing), a temporary file will be created with it as it's contents and then sent.
**************************************************************************************************************************
Recipients = DSGetParamInfo(DSJ.ME,"RecipientList",DSJ.PARAMVALUE)
Subject    = DSGetParamInfo(DSJ.ME,"Subject",DSJ.PARAMVALUE)
MessageFile= DSGetParamInfo(DSJ.ME,"MessageFile",DSJ.PARAMVALUE)
RemoveTempFile = 0

* Only send if there are recipients.
If Recipients = '' Then GoTo Eoj

MailServer ='mail-relay.bwes.net'
MailProgram='D:\Ascential\DataStage601\Engine\bin\dssmtpmail'
From       ='DataStageServer'

Call DSU.ExistFile(MessageFile, FileExists)
RemoveTempFile=0

If Not(FileExists) Then
   Message=MessageFile
   MessageFile='Message.tmp'
   RemoveTempFile=1
   Call DSU.WriteMessage(MessageFile, Message)
End

BaseCmd=MailProgram:' -server ':MailServer:' -from ':From:' -subject "':Subject:'"':' -message ':MessageFile

Temp=Recipients:','
n=1

* Loop to send mail individually to all recipients.  
* This is due to a problem with the DataStage command line utility dssmtpmail

SendMail:
To=Field(Recipients,',',n)

If To <> '' Then
   Cmd=BaseCmd:' ':To
   Call DSLogInfo("","Command=": Cmd)
   Call DSExecute("DOS", Cmd, OutputText, SystemReturnCode)
   Call DSLogInfo("","Output=": OutputText)
   Call DSLogInfo("","ReturnCode=":SystemReturnCode)
   n = (1 + n)
   Goto SendMail
End

Eoj:
If RemoveTempFile Then Call DSU.EraseFile(MessageFile,0)

[olgc]

[quote="1stpoint"]This may work on unix, but definitely not on Windows.
I ended up writing a SendMail batch that accepts a recipient list and
sends an email to everyone on the list (independently).


[code]
**************************************************************************************************************************
* Batch:SendMail
* Sends mail to multiple recipients using the DataStage sendmail utility.
* Version 1.0
*************************************************************************************************************************
* Synopsis:
* The MessageFile parameter can be a FileName or a literal Text Message.
* If it's a valid (existing) filename, it's content will be sent as the message body.
* If it's a Text Message (non existing), a temporary file will be created with it as it's contents and then sent.
**************************************************************************************************************************
Recipients = DSGetParamInfo(DSJ.ME,"RecipientList",DSJ.PARAMVALUE)
Subject = DSGetParamInfo(DSJ.ME,"Subject",DSJ.PARAMVALUE)
MessageFile= DSGetParamInfo(DSJ.ME,"MessageFile",DSJ.PARAMVALUE)
RemoveTempFile = 0

* Only send if there are recipients.
If Recipients = '' Then GoTo Eoj

MailServer ='mail-relay.bwes.net'
MailProgram='D:\Ascential\DataStage601\Engine\bin\dssmtpmail'
From ='DataStageServer'

Call DSU.ExistFile(MessageFile, FileExists)
RemoveTempFile=0

If Not(FileExists) Then
Message=MessageFile
MessageFile='Message.tmp'
RemoveTempFile=1
Call DSU.WriteMessage(MessageFile, Message)
End

BaseCmd=MailProgram:' -server ':MailServer:' -from ':From:' -subject "':Subject:'"':' -message ':MessageFile

Temp=Recipients:','
n=1

* Loop to send mail individually to all recipients.
* This is due to a problem with the DataStage command line utility dssmtpmail

SendMail:
To=Field(Recipients,',',n)

If To <> '' Then
Cmd=BaseCmd:' ':To
Call DSLogInfo("","Command=": Cmd)
Call DSExecute("DOS", Cmd, OutputText, SystemReturnCode)
Call DSLogInfo("","Output=": OutputText)
Call DSLogInfo("","ReturnCode=":SystemReturnCode)
n = (1 + n)
Goto SendMail
End

Eoj:
If RemoveTempFile Then Call DSU.EraseFile(MessageFile,0)
[/code][/quote] :But the command is "DOS", how does it work with UNIX?

[1stpoint]

olgc,
The batch is for a DOS environment although I'm sure it can be easily modified for unix assumping dssmtpmail is used.

[Teej]

Just to clarify what Amos said...

You have no flexibility with the Notification stage. You cannot use job parameters anywhere in it, surprising as that sounds.

I take it that the non-usage of #'s would not work for this? Of course, we don't use quotes in that stage, so there's no sure-fire way to tell... That sucks.

Of course, we use a third party tool to handle reports.

-T.J.

[chulett]

I did get to play with this and there does not seem to be a way to use parameters at all - encased in #s or not - in the notification stage.

Be curious to know what third party tool you are using for reports, TJ.

[Teej]

I did get to play with this and there does not seem to be a way to use parameters at all - encased in #s or not - in the notification stage.

Be curious to know what third party tool you are using for reports, TJ.

We basically use Appworx and a self-grown method to handle logs using dsjob. I always wanted to do it in C++, but I doubt it would provide much of an improvement on performance -- it takes a LONG time to pull large logs from DataStage using the commands from dsjob. In order to really boost performance, I would have to really understand Universe to figure out how to bulk fetch all of that, and slap it into a format ready for use by Appworx.

Appworx really works well especially in a mixed environment like our -- DataStage, Cold Fusion, Pro*C/C++, and PL/SQL scripts. All put together form the massive "Data Factory" project.

Of course, there may be better tools out there, but I'm not the one holding the purse strings.

-T.J.