Problems with DTS package

I am working on sql server 2000.

here is my question.

How can I update a table from the data in a text file.

I created a DTS package to do this job. I am able to insert the data from text file to a table perfectly. But What I want is to update the existing row instead of inserting a new row.

Here is sample data:

text file:

col1 col2

1 A

2 B

data in the table:

Col1 Col2

1 C

2 B

Now I want to update the first row for which the col2 is not matching. How can i do this. I wrote a activex script (for transformation) to update but it alwasy trys to insert rather than update, Here is the script.

Function Main()
if DTSDestination("Col1") = DTSSource("Col001") and DTSDestination("Col002") <> DTSSource("Col002") then
update DTSDestination
set DTSDestination("Col002") = DTSSource("Col002")
end if
Main = DTSTransformStat_OK
End Function

Thanks in advance

The script code that you posted should actually error out. It contains VB code with TSQL code. In any case, it looks like you should use a staging table approach and simply insert all the rows into server first & then use DMLs. This is the most efficient way. If you do it from the DTS package you are doing row-by-row processing and performance will suffer. Below are steps

1. Create staging table on server that contains same structure as destination table

2. Create a SP that does the following:

begin

update your_table

set Col2 = s.Col2, Col3 = s.Col3, ...

from staging_table as s

where s.Col1 = your_table.Col1

insert into your_table (Col1, Col2, Col3, ....)

select s.Col1, s.Col2, s.Col3, ...

from staging_table as s

where not exists(

select * from your_table as t

where t.key_col1 = s.key_col1

and ....

)

truncate staging_table

end

Now, modify your DTS package to do the following:

1. Bulk insert into the staging table

2. Call SP to update data from staging to main table

|||Thanks a lot for your advice