Sending an E-mail if wrong data is coming in

[I_Server_Whale]

Hi All,

I need to do this. I have seven jobs. These seven jobs are sequenced in a master sequence. The jobs do the follwing:

1. Job1 --- pulls data from an ODBC connection, parses it and loads it into a flat-file.

2. Job2 ---pulls data from a ODBC connection and loads it into a hash file.

3. Job3 ---pulls data from a ODBC connection and loads it into a hash file.

4. Job4 ---pulls data from a ODBC connection and loads it into a hash file.

5. Job5 ---pulls data from a ODBC connection and loads it into a hash file.

6. Job6 ---performs look-ups on all the above hash files using the flat-file (in job1) as the source file.

7. Job7 ---Master sequence which sequencesthe job as below:

Job1-->job2-->Job3-->Job4-->Job5-->Job6

Questions:

1. Will the jobs abort if the source ODBC is down and datastage is not able to access the data files?

2. If the value i'm looking up is not found in the hash file, How can I send this source record to a reject file and simultaneously notify by e-mail?

3. If suppose, the source data has character coming in instead of a numeric, how can I send this to a reject file and again notify by e-mail that these records were rejected?

Please note that it is a windows environment, not UNIX.

Please help me out!

Thanks much,
Naveen.

[logic]

Hi naveen,
1. yes.
3. you can develop a logic for the reject link constraint by doing a comparison using DIGITS or LETTERS transform at job level.
Not sure about best way to simultaneously sending mail part of question2.
Thanks,
Ash.

[pnchowdary]

Hi NaveenD,

To the best of my knowledge. Sending an email simultaneously after you reject a record is not possible. It would have to be done in a two step process.

1) Write all the records which don't meet your criteria to the Reject Report
2) After the job finishes, use a Notification Activity in the sequencer and attach these Reject Reports in it and mail it.

If suppose, the source data has character coming in instead of a numeric

If you meant that If you get anything other than numerics in your input, then reject it.

1) You can write a simple routine and call it from the Transformer, wherein you loop through each character and test whether its a number using Num and store it in a temp variable

2) After you loop through the entire string, do check the length of the temporary variable, with the length of the original string

3) If its same, then there are only numerics in the string else not.

It might not be the most efficient way to do it, but this is the idea that I could get from the top of my head.

[I_Server_Whale]

Hi pnchowdary,

Thanks for the response. I still have some questions. It would great if I could get some help on them.

1. How do I generate the reject report?

2. I don't know whether I'll be able to use the 'Notification Activity' stage, because the protocol we are using is INET and not SMTP. I still didn't try giving the IP address as you advised. I'll try that and let you know.

Thanks much,

Naveen.

[pnchowdary]

If suppose, the source data has character coming in instead of a numeric

Another easier way to reject a column based on whether it contains numerics only is

Code: Select all

Len(InputString) <> Len(Oconv(InputString,"MCN"))

[pnchowdary]

Hi NaveenD,

1. How do I generate the reject report?

I really didnt understand what you meant by this, but this is what I do.

I usually send the records that don't meet my criteria to a reject file ( sequential file with .csv extension and make sure you check the box that says, first line is column names).

When someone wants to look at that reject report, all they need to do is double click on it and it will open the file in excel, that will contains all the reject rows (column names + data)

A side note to it is that, if you don't check the first line is columns option, then the person looking at the report wont have a clue about what data he is looking at.