Import improvement

I have created a Data Transformation Service (DTS) in SQL Server using the import/export data wizard, saved it as a Visual Basic file and then upgraded it to Vb.Net.

I am importing data from an excel file into a SQL Server table.
The basics are working fine, but I now want to improve the importing function.

I want to disallow duplicate entries so that the same data cannot be entered more than once - except for if the data for a specific record has changed - and in this case I want to add the record, and move its original value to another table.

Does anyone have an idea how I can go about this?

Use a permanent working table to upload the data, then create a stored procedure that matches the working table against the data table(s) - using set-based queries will quite probably improve your performance.

Okay, not exactly a DTS-based solution but at least one that you can really fine-tune.

It is dts solutions if you put your sp execution into Execute SQL task.