Daily Flat File Database Update Via FTP

From what I've read, DiscountASP does not allow the use of xp_cmdshell or DTS / BCP, etc. I also understand that instead of creating SQL Server jobs, you are supposed to use the task scheduler control panel to initiate a call to a web page or web service that will complete the task of a job.

Given that, is there any possible way to do the following with shared hosting in a fully automated fashion on a daily basis:

1. Download database updates in flat files that are on an FTP server.
2. Add the data in the text file to the database.

The only way I could think of is to have a process that runs on my local home desktop PC that pulls the file to my local C: drive and then calls a web service on localhost which connects to my discountasp database and literally iterates through the records one at a time and does the appropriate update and/or insert for each record in asp.net server side code. This would be extremely slow and put a tremendous unnecessary load on the database server; plus the process would rely on my home machine being on and connected to the Internet which is a real hack.

Ideally, I'd like to have a sql job that uses xp_cmdshell to ftp a file to the discountasp server and use a dts package to import the raw data into a staging table and then perform the neceessary sql commands between the staging table and the other tables in my database; but it looks like that is prohibited for obvious security reasons. Is there a creative work-around that anyone can offer up that would be more efficient and reliable than my home PC one record at a time hack?

 

Hi,
You could pull this off with LINQ to SQL and the DASP Schuled tasks.
MSDN has lots of LINQ examples on-line now.
Salute,
Mark

 

Thanks,

It looks like LINQ to Sql would require migrating up to the latest .NET Framework. Not ready to go down that road at this point. Also, it doesn't appear to provide any kind of bulk insert ability or anything that couldn't be done with existing ado.net.

 

True.
What I left out was the mediator...I had an idea of using LINQ to SQL with XML as the flat file.
Just a prototype thought...I'm already testing something simular with LINQ and XML, but for hits.
Just thinking out loud...
Salute,
Mark

PS - I'm not a DASP employee;I'm a Technical Evangelist for Microsoft technologies.