Even under ideal conditions, importing data into SQL tables is fraught with challenges unless you can guarantee that all records in the import are new. The simplest way to meet that requirement is to import into a temporary table, and feed the table to a stored procedure that checks for duplicates and either updates the existing row or discards the duplicate record, ideally accompanied by an exception report that makes its shortly into the hands of a human.
The last time I looked at DTS packages, Excel was a potential export format, but not an import format. This is fair enough, considering that you can't just point to an Excel file and say "import this," because Excel documents are complex files that look more like a multi-part MIME message than the flat files that are usually the subject of import procedures.
In the real world, an application that imports from Excel typically imports the contents of one or more of its named range. Alternatively, the named range is saved as a text file in some format, such as CSV or tab delimited, that lends itself to importing into a SQL data base. When I need to export data from Excel, I usually want only a sheet or two, or a major part thereof, from a book that contains many other sheets, which may feed data into the range of cells that are bound for the SQL data base, or may be essentially unrelated, at least the way that term is applied to a relational data base. In such cases, I usually write a short program to run through the range, and copy it into a text file. (Actually, it's simpler than that; I wrote that program long ago, as a subroutine, into which you pass the names of the source range and destination file.)
Another possibility is to have the Excel document incorporate an ODBC connection that maps the range to an updateable view of the input table, to which is attached an insert trigger that merges new records into the permanent tables.
The best approach depends largely on the needs of your application, including, among other things, what happens to the data once it gets into the SQL Server data base, and whether the workbook is a template that is populated, fed into the data base, and discarded, or a living document that is updated periodically.
Submit an article or tip