Scheduled importing of CSV file using DTS or SSIS
Posted on 2009-03-30
I have a business partner with FTP access to our server. He has his system set to automatically FTP a CSV data file to our server. I need to read this file periodically and import or update his existing records per the CSV file. New files will be FTP'd every day for example and will have a unique name of some sort. I don't think this is a complicated project as the application is very much just a web based (dotnet), 15 field data storage and recall application.
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. Client will be sending their PrimaryKey with their records so I will know if their records already exist, will just need to write the code for that (is this where the DTS/SSIS part comes in?)
... so, after thinking out loud :) I need:
1) more education on implementing DTS or SSIS for use with CSV files (we have MS SQL Server 2000).
2) scheduling DTS/SSIS packages for execution as SQL Agent job.
3) a way to know which CSV files should be read next at the next scan
4) how to delete the CSVs after they are imported (or at some later date if a recommended)
5) a way to distinguish which records need to be imported and which ones need to be updated
6) how to execute the importing/updating after records are identified
** sounds like the DTS/SSIS "package" may take care of 5 & 6.
I know there are many existing questions about DTS and importing, but the information is scattered. I wouldnt mind if part of the solution points to other quality solutions, articles, websites and best practices for solving this project.