DTS Jobs Fail 3

[dyarwood]

I have created up a set of DTS tasks. Using these I have set up approx. 20 jobs. The general structure is

Drop Table in 1st SQL DB
Create table
Populate from Progress DB
Drop Table in 2nd SQL DB
Create Table
Populate from 1st SQL DB (with some transformations of data)
Truncate Table in 3rd SQL DB (connecting over the net)
Populate from 2nd SQL DB

These jobs work for 99% of the time however occasionally the jobs fail because they have not dropped the table in one of the databases or it has not created the table after the drop.

The jobs run the next step on success of the previous and the transfer of data should only happen on the success of the create.

Could anyone suggest any reason why the job sometimes does not see the drop step or why the create part of the DTS does not fall over when it fails.

Cheers

dyarwood

 

[dbomrrsm]

not sure why this is happening but could suggest only truncating the tables rather than dropping then they you are not dependent on a table create and there is always a table there to take data.

[blue]DBomrrsm[/blue]

 

[dyarwood]

A good suggestion. Will give it a go on my test system. The problem could be with that would be if the table fails to truncate and then doubles up the data. I will have a look into the truncate. Cheers

 

[yksvaan]

I have experienced that DTS just sometimes does not fail the task (actually the step I think) even if the task does not perform. Truncating is brilliant choice.

What I have done, you could also make a check task/step after truncation that your table is really empty. If it's not package will fail. Create a SQL task, which would look like this:

declare @anyrows int
SELECT @anyrows= COUNT(*) FROM YOURTABLE
IF @anyrows<>0 RAISERROR('Buggers', 16, 1) WITH LOG

This will add an entry in sql log and windows application log (which a systems management could trap and notify).
You should also edit the task's workflow properties and in options tab chech "Fail package on step failure" to really fail the package.

Cheers

 

[EdwinGene]

This doesn't solve your problem, but just as a side note you should remember that there is a difference between a DTS Step succeeding or failing, and the results of that step being a success or a failure.

For example, I have executed an FTP process with both directly using the DTS Execute Process Task and indirectly by calling an FTP bat file called with the xp_cmdshell stored procedure called from an Execute SQL Task. In both cases all DTS cares about is whether or not the call to FTP or the call to the stored procedure succeeded. DTS couldn't care less if the actual FTP process failed or succeeded. All DTS cares about is whether or not the DTS part of the process was successful.

In your case, all DTS cares about is whether or not it was able to call the Drop process successfully. DTS doesn't know and doesn't care if the table was actually dropped. You have to check that separately from just executing the drop process.

 

[yksvaan]

EdwinGene, you are of course exactly right about that step wont't fail even if the task activities do not succeed. Your ftp example is great and easy to understand, how could dts know if ftp succeeds or not. That was my idea too, but not in so many words. What I suggest is that one should check the error/return code of the task's activities, pass it on maybe on several calling 'shells' and trap it on the task level, and if necessary try to make the step/package succeed or fail, something like
DECLARE @baddog int
EXECUTE @baddog=master..xp_cmdshell 'something ',NO_OUTPUT
IF @baddog<>0 raiserror(' ',16,1) with log
Well, probably the idea in my previous post will not solve dyarwood's problem, it's just an extra check, but do you think it won't work (I would be glad to know if there's a flaw).

What I sincerely think though that why to drop/create tables (unless..), is it more likely to fail. Which might be the primary reason to problems, why dbomrrsm suggested truncating to get rid of problems with dropping tables.

Best Regards

 

[EdwinGene]

yksvaan:

I am of two minds about raising an error in a DTS package.

On the one hand, raising a DTS error creates an immediate and very visible red flag that something did not go as planned. However, you must be aware that you are short-circuiting the DTS process, and that there may be cleanup or other processing that would have taken place after the error point that now will not be performed because of the short-circuit. Abnormal termination of a process, even one programmed into the process, makes me uncomfortable.

On the other hand, if the DTS package itself did not fail I personally think it's better to do result checking in the DTS package; inform the user of any unexpected result through the use of EMail, a Log Table, or some other means; and then allow the DTS package to complete normally instead of raising an error and having an abnormal termination.

However, I also think this is a choice that needs to be made by the analyst and the user, together.

 

[dyarwood]

All,

Excellent. An excellent answer and one I will use when I return to work next week. Will add in error checks to the jobs. Thank you.

Stars for all. I'm kinda being thrown in the deepend becoming the SQL Server 2000 dba but hopefull I'm getting there.

 

[yksvaan]

EdwinGene, I highly value your opinions. I was not detailed enough what I was chasing (as we say here) and missed one thing about "fail package ..". I'll be back shortly.

Cheers

 

[yksvaan]

I don't see this way as Abnormal termination, in a way that execution will break and nothing shows that so happened. I will try to be more precise:

You can use Raiserror(' ',16,1) on Execute SQL Task (sql2k) without the "Fail package on step failure" property to tell dtsrun the step failed. From this step you can then take Succeed-path or Failure-path to do cleanup. So that won't abort, this concept I missed from my previous post. Raiserror(' ',1,1) will always Succeed, but you can use that to make log entries.

But, maybe after things-gone-wrong cleanups you want to tell SQL Agent (if you started package with dtsrun from there) that your package failed. After a successful cleanup step the status of package is not failed. So you can after that have Raiserror(' ',16,1) as the last step of "failure path", with that step's "Fail package.." property set. Then SQL Agent knows the package failed in case you need that flag to choose what job-step to go next in the job. Of course, there are various ways to handle things.

One more thing. You can fail a Execute SQL Task step querying also from other/ODBC sources in a silly way (but it seems to work), division by zero, which we have been avoiding for so many decades.
SELECT 1 / FLAG FROM RUNSTATUS
If FLAG=0 means not ok, step will fail and you can take Failure-path in dts package to sort it out.
(As side-effect though might get error entries to the datasource's log.)

Cheers

 

[yksvaan]

Might add I could not agree more to that user and/or systems management should be notified with like email. So what I was suggesting is to actually make a normal termination, but in most of my cases I have to tell SQL Agent that job-step failed..

I am sorry "my story" comes in bits and pieces, as what comes to communicating maybe I have a little language barrier to tackle with in these postings..

to dyarwood , just to be clear, raiserror doesn't trigger termination in it's own right, it sets a red flag as EdwinGene explained earlier, try
use pubs
select count(*) from employee
raiserror('hi pal',16,1)
select count(*) from employee

Ok, think I got it now, I won't drag this any further in this thread. Hope it made any sense.

Cheers

 

[dyarwood]

If any of the steps in a job fail I do get an email sent to me (just as an alert). I will have a look at the advice in this post as think there is a lot to look at there. Thanks for all your help.

dyarwood