December 28, 2001 at 7:04 am
I currently have a DTS package with a TSQL Statement that will insert data from one table to another. On the destination table I have an "ignore Dupe" index derived on all the columns. When ever there are dupes, TSL returns a msg of, Dupes Found. DTS looks as this as a failure. The package still runs but shows up as it failed. From my experience, any return, triggers the DTS package to fail. Is there any way I can compensate for this message and prevent the DTS package from failing?
-JG
December 28, 2001 at 2:09 pm
Not sure how DTS works, but this is exactly how it works in ADO as well. Even though the operation itself is fine with the ignore dupes option the error still gets raised on the client - typically I trap for that particular error and just do resume next.
Andy
December 28, 2001 at 4:32 pm
If any task in DTS fails, you get a failure. Annoying when you don't care about certain tasks. I've setup an alert(email) in the package that let's me know nothing is wrong.
Steve Jones
December 28, 2001 at 9:15 pm
Thats why as interesting as DTS seems, I can never get that interested - seems like so many quirks. The cost of trying to create a super high level environment. Once you hit a separate level of complexity (and it depends on the TYPE of complexity) you're better off building a solution in a real language - even if that means using DTS as part of the solution.
In this particular case you could modify your process to only append new and unique records - easiest way would probably be an outer join between new data and the target table.
Andy
January 2, 2002 at 9:19 am
In addition to using an e-mail to tell me everything in a DTS package succeeded, I also use extra tasks and/or SQL to insert lines into a logging table to tell me if each task succeeded or failed and sometimes I capture the number of rows inserted or updated and place that number in my logging table. That way I can scan the complete activity of the DTS package after completion and while it is being run by a JOB I can check its progress.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 2, 2002 at 10:12 am
That's a good idea, but I tend to agree with Andy. I constantly run into places where DTS falls short and I have to accept a less than bulletproof solution or rewrite something in T-SQL/VB. I'm looking into building my own custom tasks to helpsolve this from VB.
One thing that has helped is using one package to run another. Then I can trap errors and decide whether to report a failure.
Steve Jones
January 2, 2002 at 10:18 am
I agree with you Steve. I had no idea there were a lot of quirks in DTS. The one post on using other tables and pumping out emails to monitor the job are nice, but I am trying to not monitor this job, just want it to page me when there is a real error, not when it finds dupes. I get paged every day on this job and it gets old. It looks like the solution will be to build another app to monitor the DTS job.
-JG
January 2, 2002 at 10:45 am
Some thing else I do to eliminate getting an error response from a job running a DTS package is to have a second step in my jobs. This step sends me an e-mail telling me the DTS package failed and then quits reporting success. The first step is designed to go to the next step on success or failure. If its less trouble than developing what has been discussed above you could design the second step to check the logging I talked about in my previous reply and if there is a failure in a step other than with the dups send an e-mail reporting failure. I chose this kind of solution because I don't have enough knowledge of programming languages to develop any other solution.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 2, 2002 at 11:27 am
That's a good solution that solves part of the problem. Personally, I think this is an area that SQL Server 2002 could greatly improve, but we shall see.
Steve Jones
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply