Send an email from datastage job

[shrey3a]

Hi,

I've below scenario :

There is a reject link in my job populating the error file and I need to send an email to group of people after the job has finished/ aborted only when the data comes to reject link.

for e.g. I extracted 1000 rows and 998 made to target and 2 is been sent to reject link than I need to send an email. If all 1000 rows reaches target then I do not need to send the email.

* Due to certain limitation we can use sequencer jobs , I need to embed the logic in my datastage server job only may be I can have another job which can look at previous job reject link and if value @inrownum for reject link is >1 then shoots an email.

But I do not know how to implement it in Datastage.

Can Gurus help on this

Rgds,
Munish

[pnchowdary]

Hi Munish,

The same topic has been covered in great details earlier. Here is the link for it

viewtopic.php?t=94042

[shrey3a]

Hi ,

But in my case the reject link feeds to oracle table so I need to check if the data is been passed thro' reject link then only do an email after the job is finished.

Rgds,
munish

[pnchowdary]

Hi Munish,

Well, I am confused now

Is your reject link writing to a sequential file or an Oracle table?

[shrey3a]

[quote="pnchowdary"]Hi Munish,

Well, I am confused now

Is your reject link writing to a sequential file or an Oracle table?[/quote]

Its writing the Oracle table.

[pnchowdary]

Hi Munish,

Hopefully, if you have any metadata column in the reject table, that can uniquely identify the reject records inserted in a particular job session.

You can use an user defined sql in an Oracle DB stage and get the counts of the number of reject records inserted into the reject table, for that particular run of the job. Then you can test whether that count is more than 0 and send an email or not depending on that criteria.

[shrey3a]

Hi,
Is there is way we can identify in Ds that particular link rownum >1 and then send an email.

It may be in the same job or other job which checks the previous job link info for the same.

Rgds,
Munish

[vinaymanchinila]

Hi,
You can write it parallely to a flat file also and have a transformer where you can trap the number of rows.
Then in the transformer use the following condition

If Inrownum>0 then (DSSendMailTester('xyz@yahoo.com', 'ds@xyz.com', 'RowsRejected', 'xyz.yahoo.com', "dstest")) else '0'

[shrey3a]

where in transformar I use the condition in constraint link or stage variable....I'm struggling on it

[vinaymanchinila]

In the transformer , on the output side you can use I guess, I have not tried it, let me check it and let you know. In themean time if you can get it done let us know.

[vinaymanchinila]

Hi,
1) Add another transformer and a flat file to your job.
2) In the transformer, insert a new StageVar and the assign the system variable @INROWNUM
3) Have a dummy column for the output flat file say 'Count', in the value field for that use

If StageVar>0 then (DSSendMailTester('vmanchinila@rapidigm.com', 'ds@rapidigm.com', 'test', 'abc.rapidigm.com', "dstest")) else '0'

This is working but it is sending an email for every record so you need to figure out a logic on how many u want.

[shrey3a]

Thans it worked with code and sending one mail for any no of rows


If @INROWNUM =1 then (DSSendMailTester('munish.sharma@anheuser-busch.com', 'munish.sharma@anheuser-busch.com', 'test', 'mail.anheuser-busch.com', "dstest")) else '0'

Thanks again for all help

[vinaymanchinila]

How did you restrict it to send just oneemail instead of say 10 emails for 10 records?

[pnchowdary]

Hi Vinay,

The @INROWNUM =1 condition that he used prevents from sending the email 10 times.

[vinaymanchinila]

Thats cool!