1) more education on implementing DTS or SSIS for use with CSV files (we have MS SQL Server 2000).
SQL 2000 > DTS
SQL 2005 > SSIS
So you'll need to use DTS at this point. If you are considering an upgrade to SQL 2005 in the near future then I suggest you do this in SSIS on SQL 2005 as the updgrade path from DTS > SSIS is not always simple.
2) scheduling DTS/SSIS packages for execution as SQL Agent job.
You just have to keep in mind that the package runs ON the server (all paths need to be as though you are on the server), and also remember that it runs as the SQL Agent service logon user.
3) a way to know which CSV files should be read next at the next scan
Usually once you successfully import a file you move it to an archive folder. So you never import them again
4) how to delete the CSVs after they are imported (or at some later date if a recommended)
Move them to an archive folder.
5) a way to distinguish which records need to be imported and which ones need to be updated
As you have surmised, as long as the primary key is in the source file, thats pretty easy. You should definitely make use of a staging table as acperkins has suggested.
6) how to execute the importing/updating after records are identified
As acperkins has suggested, you import them all into a staging table, then compare.
But it is very important to note that implementing these kind of solutions (looping through source files) in DTS requires T-SQL or ActiveX scripting, and this does not migrate very well to SSIS. If you have the opportunity then I strongly suggest you upgrade to SQL 2005 and do this in SSIS.
Upgrade path is simple (backup from SQL 2000 and restore into SQL 2005). Then of course there is regression testing if you want.
Steps 3,4,5 can be fundamentally different between DTS and SSIS. i.e. for SSIS it can in some cases be practical to not use a staging table, and looping and archiving files requires a lot less work in SSIS.
Here is a sample page for looping and importing files in DTS just to give you a taste:
http://www.sqldts.co
It is basically a DTS 'hack'
by: acperkinsPosted on 2009-03-30 at 19:48:10ID: 24025356
>>The most complicated part (for me anyway) may be trying to figure out whether I should be updating or adding new records based on the clients CSV.<<
Actually that is the wron approach. Do not import directly into Production table(s). Always import into a staging table and then use that to validate the data before you insert and/or update into the final tables.