Can DTS respond to an e-mail?
-
Hello there.
My subject title may be a little misleading.
I have a business problem in which CSV files will be mailed to an e-mail address each day and I want to append the data in the CSV file into a table in a SQL Server database.
I haven't used DTS much but I'm thinking this is possible. My goal is to get the data into the database without any human intervention. I'm not sure what the best approach is. One suggestion is to have the e-mail system some how kick off a SQL Server job that runs a DTS package, which loads the data.
I may be able to get the CSV files via FTP instead, if that would make things much easier.
We're using MS Exchange.
Any ideas as to the best approach to this? Thanks.
-
Getting the file via FTP would vastly simplify matters. Without using FTP you'd end up with too many steps in the process that could fail. Watch for the email, extract the file, trigger the sql job, process the file. You can use the inbuilt FTP task, or you can use the much more functional custom task available from http://www.sqldts.com
One point I would make is that once you ahve the CSV file, load it directly into a staging table in the database first. Don't try to validate information as you're importing it and don't use the Data Driven Query task to make updates directly to your production table. That will just slows the import to a crawl.
This approach will allow you to run T-SQL statements across the data and perform bulk updates to ensure appropriate business rules are satisifed. Then run delete/update/insert statements to update the production data.
--------------------
Colt 45 - the original point and click interface
-
Many thanks. I'll check out that custom task you refer to.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply