Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: Error trapping in MS SQL 2000 DTS package

    I need to modify a DTS package with several tasks and have it continue executing even if it encounters error in one or more tasks. The error being encountered is during insert statement (null issues, data type issues, etc.). The DTS stops exectuing and will not continue with the next task if error is encountered.

    My question is...is there a way where the tasks wil continue executing and just ignore the line/record that causes the error? One of the solutions I can think is to add validation scripts in the SQL query, but it will take a lot of time modifying all the queries. I have lots of DTS packages & lots of steps/tasks with each DTS package. I'm looking for a more simple way to do this. Hope you can help me. Thanks a lot.

  2. #2
    Join Date
    Oct 2005
    Posts
    119

    MS SQL 2000 - A function that would test the INSERT statement and return the errorno

    Can i create a function that would return the error no. of a certain sql statement (insert query) that i pass to it? How then? Can you show me the syntax pls.

    I have a DTS package with several tasks. SOme of the tasks insert data to table. But if the INSERT statement results to an error, the DTS package stops executing. What i want is for the next tasks to continue even if an error is encountered. SO i would like to test the INSERT statement first before executing it.

    Hope you can help me. Thanks alot.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Several points:
    No, not a function. But you could do it with a sproc.
    However, I would argue that much of the issue here is adding complication and inefficiency to overcome the devil that is DTS.

    There are many on here that would recommend you do all your inserting and updating in T-SQL. I'm one of them. Apart from anything else, DTS is now defunct - there have been TWO subsequent versions of SQL Server on which is is now deprecated (2005 & 2008).

    A final thought, if you are determined to persevere with DTS despite guidance not to - isn't there some sort of control-of-flow that causes execution to follow paths based on success, failure or irrespective of success\failure?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I noticed that you repeated your question - please don't repost the same question. I have merged the threads.

    I suspect much of the reason you got little response is because you are using an out of date technology that most of us never had much use for in the first place. If you get an introductory book on SQL Server 2000 BI, you'll probably find about one third of it is dedicated to error handling and parameter passing in DTS - this in itself is a red flag!

    Anyway, you'll probably do better on a specialist site for DTS error handling techniques:
    SQLDTS.com - Data Transformation Services on the web
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DTS doesn't do row-by-row error handling - if a single row in a 40 line insert fails, none of the 40 rows will be inserted.

    However if you were on SSIS, the behaviour is different. You have the option of ignoring or redirecting error rows, meaning that 39 of the 40 rows would insert correctly, and you can decide what to do with the one that would fail.

    The only way to mimic this behaviour in DTS is to write a loop / cursor to run every row as a separate batch.
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2005
    Posts
    119
    I understand, I proposed that solution (stored proc) to my boss and also the modification of the queries wherein validation scripts will be added. But they didn't approve that, what they want for now is a simple or immediate solution that will not require much time. I was just thinking if a user-defined function can do the trick. Thanks a lot for anwering my queries.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How would a function do the trick? can you give us a rough idea of what you intend it to do?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by LimaCharlie
    I understand, I proposed that solution (stored proc) to my boss and also the modification of the queries wherein validation scripts will be added. But they didn't approve that, what they want for now is a simple or immediate solution that will not require much time. I was just thinking if a user-defined function can do the trick. Thanks a lot for anwering my queries.
    You cannot perform data modifications from UDFs. So no - that won't work.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2005
    Posts
    119
    I'm really not sure if this will work. I just want to call a user defined function or stored proc that would test the query and return the error no.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by LimaCharlie
    I'm really not sure if this will work. I just want to call a user defined function or stored proc that would test the query and return the error no.
    This is more difficult and more vulnerable to error than calling a procedure that just does the modification.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Oct 2005
    Posts
    119
    Ok, here i go again. (peace!)

    So i have a dts package with several tasks/steps. In some tasks, it inserts data to a table. If the INSERT statement fails, no data will be inserted and the whole package stops executing. I know I needed to revise the whole jobs and dts packages and use stored procedures instead. But temporarily, what i did was i added an object after the INSERT task and used the "on failure" precedence/workflow. (There is another precedence which is the "on success" which then points to the next task.) The object I added is the "Execute SQL task", it only calls/executes the stored procedure. The stored procedure is the one doing the error trapping and inserting of records row by row. I used @@error in the stored proc, so if @@error<>0, the data will be inserted to another table w/ no constraints. I tried executing the storedproc in the Query Analyzer, it works perfectly. But when I run the DTS, not all valid records were inseted into the table but the invalid record was inserted to the errortrapping table. I assume the object "Execute SQL task" can't do the trick, so I'm thinking of using ActiveX Script. Im not that familiar though w/ activex script. What do you think? I need you expert advice. Thanks so much for always answering my queries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •