Using DTS to verify data with success/failure precedences

  • I wonder if anyone can help. We are using SQL Server 7.0 SP2, and I have been using DTS now for a number of years to import EDI data etc.

    I recently tried to set up a package where the outcome of one of the tasks (task 1) could either result in success if data was ok, or failure if data was invalid. I set a success workflow to the next task (task 2), and a failure workflow to an email task to let me know it had failed, followed by a completion from the email task to the next task (task 2).

    When I run the package if the task 1 data has a problem it follows the failure route & sends the email and processes task 2.

    However if the data is ok, it stops at the end of task 1 and doesnt follow the success route.

    Can anyone shed light on m,y problem please?

    Cheers

    John Woolliscroft

  • Hi John,

    I can't help more if I don't see any code.

    Check this tutorial http://www.sqldts.com/default.aspx?218

    You probably need to reset the properties of your steps after the package ends of at the begining.

    Rgds

    JFB

  • Don't really need to see code. You can add an ActiveX/vbscript task to do a data analysis and assign a success or failure path from that task, then add a failure point(usually an email notification).

    I just learned how to do this, I check for rowcount=0, and fail it if it's true, because there is no data to process. I'm sure there are other ways to achieve this, but it's pretty clean.

    I usually don't post code, and I had to learn it the hard way, but at least it's a start

     

    Good luck!

    Peter Karhatsu

     

    hmm, just reread your post and realized the above really doesn't apply. I'd say turn on logging in your package and look at the results of each step in the package log. I really don't recall 7.0 very well, but I am pretty sure the loggin was the same.

     

    GL

  • Thanks jfb & peterus.

    I have had a look at the link to DTS site. It looks like a mine of info.

    I have turned logging on, and I can see whatSQL thinks is going on. I am afraid I am not very good at ActveX - but I will give it good looking at!

    Cheers

    John Woolliscroft

  • On further investigation after turning logging on, it forced me to look at what I was trying to do, and what I expected.

    I was wondering why task1 success didnt make task2 work, but I was looking at the wrong end!

    I should have been looking at the two constraints allowing task2 to work! I have now solved the problem.

    Thanks once again.

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply