What's the best combination of tools to import daily data feed (in .CSV format) to a MSSQL server table?
Environment and acceptable tools: - Windows 2000/XP - ruby or python
MS SQL Server is on a remote server, the importing process has to be done on a Windows client machine.
If you wanted to go the Python route, I'd look at SQLAlchemy (list of supported databases here) and the csv module in Python's Standard Library.
Personally, I'd probably use Elixir on top of SQLAlchemy as I find it a bit easier to work with on simpler things. I might also look into NumPy's loadtxt() if I could because it's got some convenient features over the standard csv module (like only reading select columns for example). NumPy might be overkill for this application though.
You can use "bulk insert" or "BCP utility".
And what about DTS services? It's integral part of MS SQL server starting with early versions and it allows you to import text-based data to server tables
If you need to make any transformation on values of the CSV file I'll advise to use one scripting language, like ruby or python to do it, otherwise use one database tool like Max said.
If you want to use ruby, please take a look at my MS SQL Server connection example here and change it according to your needs.
