Post reply

transform data task and skipping rows

  • October 15, 2003 at 2:48 pm

    #106460

    I've got a good DTS working model for the ODS and tables are refreshed each night from production (gzipped files). But one issue that breaks the load are rows in error. What I would like to do is just skip the rows, put them in a file, and move on with the load. I know I can increase the max error count to some number and that might be a decent workaround for now.

    What often happens is that a field contains the column delimeter and so the staging table fails to load. Just skipping the problem rows is the best solution in our circumstances since I can just them out the next day and fix them in production. How can I make that happen? I'd also like to avoid using fixed column length records because of the large volume of data.

    Thanks.

    Hank

  • October 16, 2003 at 11:28 am

    #478522

    I have ran into something like this before.

    I used an Active X script in the DTS.

    In the script I checked for certain values and if needed I would skip the row using

    Main = DTSTransformStat_SkipRow

    or

    Main = DTSTransformStat_OK

    Let me know if this helps

  • October 16, 2003 at 5:30 pm

    #478562

    Running ActiveX Script while importing significantly slows down the import process.

    One solution would be to import the file into a single field and then use some stored procedures to wash the data prior to inserting it into the live tables.

    Or you could do the above import, get a count of the number of rows in error and dynamically set the Max Error Count on the import process.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

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

  • October 20, 2003 at 11:21 am

    #478832

    Thanks for the help. I've discounted the ActiveX solution for Phill's very reason. It's just too slow when 50M rows are coming in (and that's just for *one* of the large tables). Our "good enough" approach remains querying the source for errors we've seen in the past and fixing the source before the extract. But I might combine it with the max error count > 0 feature. Thanks.

    Hank

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

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