Send an email from datastage job
Moderators: chulett, rschirm, roy
Send an email from datastage job
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
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
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA
Hi Munish,
The same topic has been covered in great details earlier. Here is the link for it
viewtopic.php?t=94042
The same topic has been covered in great details earlier. Here is the link for it
viewtopic.php?t=94042
Thanks,
Naveen
Naveen
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA
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.
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.
Thanks,
Naveen
Naveen
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
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'
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'
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
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.
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.
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
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
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA