Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: How to use criteria with text file source in DTS package?

    I have a peculiar problem with a database.

    I need to import data from text files where one column matches a value in a table that's already on the database. I cannot even fathom how to do this without pulling the entire 1GB file onto the database and simply deleting data that doesn't match the criteria. While this would work, I'm sure there's a more efficient way to do it! Can anyone give me some pointers? I'm not exactly a DTS expert.

    Thank you.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    for this you will have to edit the column transformation ActiveX script generated by DTS. just need to add a "if" condition to skip the row in case the condition is not matching. something like below will do the job

    Code:
    Function Main()
    	if DTSSource("Src_Col_Name")  = "Filter_Value" then
    		DTSDestination("Col1") = DTSSource("Col1")
    		DTSDestination("Col2") = DTSSource("Col2")
    		DTSDestination ...............
    		Main = DTSTransformStat_OK
    	else
    		Main = DTSTransformStat_SkipRow
    	end if
    End Function

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Is there an easier way to do this? The values I'm filtering by are held in a SQL table. I don't want to have to manually update these in the script because there will be several dozen policy IDs we're filtering by, and the table we're updating has 229 columns so that's a ton of scripting to do!

    For example, we have a 1gb file we could import, but the people who use the DB are interested only in data pertaining to 30 policy IDs. They want us to import ONLY the data for those policy IDs; they don't want to see anything else.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In your DTS function, write a few lines of VBA code to connect to the database and see if the current source row's policy ID exists in the table that holds the list of interesting policies.

    -PatP

  5. #5
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Thanks for the suggestions, guys.

    The way we ended up doing this is a very roundabout and slow. We're doing it in batches of 100,000 and putting the data into an intermediate table, running an update to put the data that matches the criteria into the final table, truncating the intermediate table, and going around to the start and doing it again until we get to the end of the file. This way, we managed to read through a 1GB text file and pull out only what we were interested in in our 200MB database. I know this is hideously inefficient and our DBAs would probably kill us if they knew about it but I did ask them for help and all they could do was point me in the direction of a couple of other programmers in the company who didn't have any ideas.

    I'm not sure if we will continue to use this solution but it is so far the easiest one to maintain that we've thought of. I won't be at this job much longer (few months at the most) and when I leave the other team members (who are less experienced with SQL Server than I am, as horrifying as that thought is) have to maintain what I've written, so gigantic long ActiveX scripts are something I'd prefer to avoid if at all possible; some team members cannot write VBScript at all. Also, the words 'best practice' are unfortunately rarely uttered here.

    Realistically we can't avoid ActiveX scripts; we have a few here and there, always to convert dates from one format to another, but the scripts suggested would just be too large for our team to handle. ActiveX definitely has its place though; I just wrote a script last week to convert Julian dates to Gregorian. No other way to do it than with a script! These suggestions may come in handy for a smaller-scale import too, so I'll tuck away a copy of the tread J.I.C.

Posting Permissions

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