Is there a good way to create a recurring import for an ASP.NET site?

Question

The site I'm working on is running Windows Server 2003 and SQL Server 8 (2000?), and ASP.NET 3.5.

I need to have some sort of script or application run to import data from an FTP'd text file, into the database. There is already a site running on the machine, that uses the current database. Can I use a scheduled task to reliably kick off some sort of .aspx page that will import the data? Or is there a better approach?

What about making sure that no one else can access the page that runs the import? I don't want random users running the import!

Thanks in advance!

P.S. some processing needs to occur on the data before its inserted. i.e. lookups, conditionals, etc, so the DB tools aren't robust enough (I think). I hate DTS, and I SSIS is not available in this version I think.

Answer 1

Why would you use ASP.NET? Depending on the complexity of the job you could either load it directly to the database (BULK LOAD) or use DTS (SQL Server 2000) or SSIS (SQL Server 2005/2008) if more complex processing is needed.

Answer 2

run a BULK INSERT or bcp to import the data instead, see here http://msdn.microsoft.com/en-us/library/aa173839(SQL.80).aspx

Answer 3

If you want to have a C# App handle your import I would suggest a windows application (exe) w/o a form (better than a console app because it does not pop up any UI whenever it runs). Have it run every so often (every minute) by a scheduled task.

Answer 4

I'll echo other people here - you don't want to have a scheduled task hit a web page. SQL Server provides some good data import options, or you could just write a simple windows program and run it as a scheduled task.

Another option would be to write a windows service that watches your FTP directory and does the import.

Answer 5

As others have said, probably a separate console application (triggered by a scheduled task) or a windows service would be the best option for this scenario.

On the other hand, if you already have all the required functionality available in the web app running on the server, then you could probably set up a scheduled task, that starts a script (VBscript, JScript), which in turn calls a page of the web app.

To have some sort of security (e.g. preventing that any user can call that page), you could add some code to the page, that checks if the page was called with http://localhost. This would at least prevent the page from being called from a remote client.

Answer 6

DTS and stored procedures in a job.

BCP and stored procedures in a job.

You say you need to do alot of lookups and conversions? SQL is good at that - and good at doing it fast. It can seem a little intimidating at first, but it's not hard.