Post reply

DTS Error Handing

  • May 5, 2005 at 10:23 am

    #362530

    Hello,

    I need a bit of help.  I have a large data load issue.  The data comes from a legacy system. The dataload consists of two stages in one DTS package:

      1. Stage 1 - DTS Data into Database 1 (Staging).  This database contains no parent child relations.  It is used to weed out any invalid records due to physical data layout issues
      2. Stage 2 - Migrate data from Database 1 (Staging) into Database 2 (DW).  This database is highly constrained.  Up until now I have been using a VB program which calls complex cursorized stored proceedures to grab the records and attempt to insert them into the DW table.  I would like to use DTS directly for this phase to gain some performance.  I have attempted to use the SQL task to "INSERT INTO" from "SELECT *" to migrate the records, however if there is a failure I am not able to tell which record fails. (if someone can suggest another method it would be appreciated)

      Occasionally, and I mean occasionally, step 2 will fail due to a set of bad records in which the parent record is missing or the child key has been mistyped.  DTS does not report the exact record which fails in the INSERT INTO" from "SELECT *" in the SQL TASK in the final stage (Stage 2) of the Data Migration.

      Without using a cursor in a stored proc and calling the stored proc to run the migration from Staging into the DW.  Is there a manner in which to migrate the Staging Records In Database 1 to the DW in Database 2 and obtain the exact row which fails (if any fail)?

      Any help/insight would be appreciated in how to capture the failed record during the migration into the DW using DTS. 

       

    1. May 5, 2005 at 5:18 pm

      #557248

      Firstly, please don't cross post.

      Secondly, see the responses over here,

      http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=180268

       

      --------------------
      Colt 45 - the original point and click interface

    2. May 5, 2005 at 8:32 pm

      #557257

      I APOLOGIZE FOR CROSS POSTING, I SIMPLY CHOSE THE INCORRECT GROUP AND WANTED TO REDIRECT THE POST TO A MORE SPECIFIC TOPIC

    Viewing 3 posts - 1 through 3 (of 3 total)

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