Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    41

    Unanswered: Filtering lines in a DTS Text Import

    I have a DTS that reads in a bunch of transactions daily to a tran history table. I read them in from a text file each day. The problem is, that about half of the lines in the text file contain semicolons because they are comments. What I do now is, import the whole thing, and then do a Delete on my tran hist table for all lines with semicolons. As my tranhist table grows, this Delete will start to take a long time. How do I filter it so it doesn't even import the lines with semicolons to start with, that will run faster and save me time later.

    Thanks,
    Andrew

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look into ActiveX for DTS, - there are many things you can do there. Also, check SQL DTS for more info.

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I assume that at the moment your tranformation from the text file to the tran history table is happening using a column copy or something similar.

    My advise would be to change this to using an ActiveX Script as suggested above. Using this method can edit the function (vbscript or javascript) so that if your line has a semi colon in it the Main function returns a DTSTransformStat_SkipRow and if it doesn't it returns an DTSTransformStat_OK status...

    Have a look at it and if you have more questions just yell...

    Of course you could all import everything in the text file to a temp table first, then you can do a sql select to exclude those with semi colons when you import from your temp table to the tran history table....

    Choice is yours really.

  4. #4
    Join Date
    May 2003
    Posts
    41
    I will try that activeX script - i was thinking of using a temporary table, but i think that would just add an extra step. Thanks for the advice, i'll try it tomorrow.

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Yeah, the temp table is an extra step but it still might be faster, witht eh activeX script your process will have to check each line individually, with the temp table you do all of that checking at once.

    You could try both and see what works best for you....

Posting Permissions

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