Sometimes automation of basic tasks is best done using SQL Server instead of writing a separate program. In this article by Matthew Mombrea, he walks us through the process of automating an FTP download and loading the data into your database.
The importance of automating processes can’t be overestimated. Most things start out as a manual procedure until you work out exactly how things should happen, but many times they stay that way permanently. What’s worse is that this manual process usually relies on one person who knows the magic sequence. This means that something as trivial as a sick day can result in a system or data problem.
If you’ve got several sources of data that you need to get into a database, you don’t have to get too fancy to automate the process. Good old FTP and flat file import will do the trick reliably and never take a day off.
Here are the basic steps to automate an FTP download in Windows followed by a CSV insert into MS SQL Server.