Hi all.

I am new to DTS and I would like some advice on how I should proceed with my
project.

Basically, I have a text file that is tab delimited, which is used to
insert/update multiple tables in SQL server. The file is huge. The sample
has about 80, 000 rows and perhaps it can grow to 300, 000 rows. I need to
process this data from client side application to insert/update. So, if I
have to process 300,000 or let alone 80,000 from the client side, it takes
many hours and even days. The file is produced by a legacy system every day
and I do not have control over how it's generated. In one word, I get all
of the data every day.

But here is the thing. There will only be a small number of changes per
each day compared to the overall number of rows. So, for example, let's say
there were 1,000 rows that changed overnight. That is more acceptable
number of rows to work with from the client side. In trying to come up with
a way to do this, I've gone ahead with creating a DTS package.

The DTS package basically should:

1. bulk insert the text file into a raw table -- "feed_raw"
2. compare "feed_raw" to "feed_last" -- basically, yesterday's feed.
3. insert any new/changed rows from "feed_raw" table into "feed_diff" table.
4. overwrite "feed_last" with "feed_raw".

steps 1 and 4 are easy. I am pondering the best way to do steps 2 and 3.
At the moment, I created a transform task, that has a ActiveX script
transform with a lookup. The lookup will return a row from "feed_last"
using a few fields from "feed_raw" -- DTSSource --, and so basically I can
compare a row from a "feed_raw" and another from "feed_last". If the lookup
returns empty variant, then I know to insert the source row. I guess it
comes down to comparing the source row to the lookup-returned row then. The
issue is that the tables contain about 150 columns. I don't want to list
150 columns down in the vbscript code if I don't have to.

Now that the scenario is known, is there a quick and easy way to compare two
rows from two different tables with same structure? And if so, how would I
incorporate it into the DTS Package?

Thanks
Jiho