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.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

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|||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.|||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|||Thanks for the suggestions, guys. :beer:

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. :mad:

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. :D