DTS-Excel to SQL Import | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS-Excel to SQL Import

I require a SQL DTS package to perform the following: – many users create excel files periodically to server
– Once an excel file has been uploaded, need to read and validate the data
– then import data into existing database,
inserting data if record is not present and
updating data if present
– errors need to be logged to database and emailed Can u please guide me how to do this using DTS in SQL Server 2000 Thanks in advance. -TFakih

What are the validation you need to check on the EXCEL file.
Possible way of doing this is import excel to temporary table then validate and import to the database you need —————————————-

The Combination of the User_ID & Group_ID should be unique.
My problem is to generate error (if any) & send it to user back,
How can I do this…can u pls.guide me. I m new to DTS.
Hi, I think you need to put a constraint (User_id and Group_id) on your destination table.
If there are any duplicate values the constraint will automatically check it and throw error. If you have a job that runs DTS, set its property to Quit the job on failure or write it to log file. Thanks, Bijesh
Bijesh, such a constraint will cause the import to fail. You need a ‘working’ table that allows you to have duplicates, and then you process the data, making sure that the duplicates do not enter into the production data.
Thanks Adriaan, I agree with you.
I was under the impression that insertion of duplicate data in DTS needs to be aborted. Thanks once again. Thanks, Bijesh
Using such constraints you can set a workflow to fail or pass the package steps in that case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.