Tracking Package Execution Status

[NickName]

Hi,

Say, you have two inter-dependent packages of pA and pB. With the
successful execution of pA you would proceed to execute pB. And since
they would perform some repeatitive tasks on regular basis they are
scheduled as a job. But you don't want to the job to make attempt to
run pB if you know pA has failed and you want to nofiy sys adm of it.
And let's say pA has log option checked (so, there's a log file for
it). Now, it would seem to me quite clumsy to check this potentially
huge file to see if a particular package (pA here) is successful before
running pB. Any other viable and clean solution?

Yes, one could possibly combine pA and pB into one big package (pBIG),
however, for the sake of a clean and clear business process this is not
desirable since pA and pB handles
totally different business processes.

TIA.

[NickName]

The title of "Tracking Package Execution Status" may be edited as "DTS
Tracking Package Execution Status" for clarity. Unfortunately I can't
edit it or I miss this function.

[Stu]

There's a lot of ways to do this; you an launch a DTS package from a
DTS package (a master child relationship), and make that launch
dependent on success or failure. OR, you can simply put each package
as a step in the job, and on failure of step one, quit the job and
notify your sysadmin.

Stu

[NickName]

The idea of DTS package inside another is interesting. However, the
key issue here is how to deliver/send an exception/error note/message
to sys admin when there's source data problem. Unfortunately the SMTP
call from sql server in my environment is disabled. I'm looking to see
if alternative is available.

Thanks.

[Stu]

On Error Scream "Help" :)

Seriously, notification is another issue altogether. However, DTS is
flexible enough that it can do most things, assuming that you have some
sort of notification structure in place. If you've disabled SMTP, how
about (ugh) MAPI? How do you normally handle notification?

[NickName]

"notificati on is another issue altogether." And yet, it's almost
equally important, imho. OK, I've just looked up MAPI a bit and played
around a bit more about mail capability from sql server. Things that
confuse me about this damn mail thing with sql server is,

My understanding is,
mail may be sent via at least following two options
*) smtp server --> mua (mail user client, e.g. Outlook);
*) exchange server --> mua

My ENV is:
XP prof; sql server 2000; network-wise, I'm on two separate domains,
one handles email via exchange server etc. call it DomainX and another
does other stuff call it DomainY

The box's NT services include smtp service running under Local System
(attempts to use either domain user account failed). And yes it says
it is running (under system account). However, this service's UI does
not include any feature which indicates what IP it uses for the smtp
server, localhost or fqdn?

sql server 2000 and sqlagent services running under DomainY/myAccount
as suggested by some material (attempt to run them under
DomainX/myAccount failed)

RPC running under Local System (default)
RPC Locator running under Network service (default)

(And I must have done something a little while ago about setting up an
MAPI client like Outlook.) Now, when I go to Enterprise Manager, under
Support Services/SQL MAIL,
I have a MAPI profile and test to success. However, however, however,
when I attempted to run some sql extended procs for mail function like
xp_startmail, xp_sendmail, xp_stopmail I ran into errors, err msgs
include "failed with mail error 0x80004005". Searching for this err
msg has not generated interesting enough info.
Though the following thread was sort of interesting,
http://groups.google.com/group/micro...2e2c92c4a7e8db
7. Michael J. McMurtrey 's writing is very good.

I suspect that it's related network security ...

FYI, I've also looked into another option, and again that may be
constrained by network security.

Any further thought?

Thanks.

[Stu]

"(And I must have done something a little while ago about setting up an
MAPI client like Outlook.) Now, when I go to Enterprise Manager, under
Support Services/SQL MAIL,
I have a MAPI profile and test to success. However, however, however,
when I attempted to run some sql extended procs for mail function like
xp_startmail, xp_sendmail, xp_stopmail I ran into errors, err msgs
include "failed with mail error 0x80004005". Searching for this err
msg has not generated interesting enough info. "

Are you logging in as the account under which the SQL Server service is
running? Be sure that you have a mail profile set up for that account,
and that profile is the one being used in SQL Mail.

[NickName]

Yes. However, this account is not for the Outlook (default). Probably
we're getting a bit closer to the source of the problem. So, I created
another account/profile under Outlook, but this new
account could not use Exchange server (that's the way things are set up
here: one account only, in normal sense perfectly correct), so I use
IMAP server for incoming and stmp for outging (thogh I'm not sure if I
have IMAP server at all, server address, initially Localhost, however,
Outlook complained not found, then I used my box's IP address. Now,
the Email account under Outlook profile has 2 accounts: one default and
another newly created (this one is probably still bogus). Still won't
work. Do I have to restart 'everything'? Somehow I strongly suspect
it's related network user permission sort of security thing.

Thanks.

[NickName]

Just an update, my alternative approach seems quite promising. Out of
box, out box!!