How to automate importing a text file to sql from web server

[noonan59]

I'm working on an asp.net application and I would like to be able to upload a text file to the web server, and then tell sql server to go get the uploaded text file and import it into a new table. After the table has been created and imported into sql, the asp.net application will then regain control and work with this new table.

This web app will be running on multiple web servers so I would need to pass along the web server name and also the file location and name.

How can I automate this process? Can I do this through a stored procedure? Or is there another way to make it work?

Thanks for any advice!

 

[dbomrrsm]

Have you looked at DTS ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[noonan59]

Yeah I figured that I need to set up a DTS. But I'm not sure how to write a stored procedure that passes the web server name and file location info to the DTS and then executes the DTS.

 

[dbomrrsm]

in dts look at the tasks - there is an FTP task which I think is the one you need - you can then provide this task all the details and schedule the DTS to run as often as you like by saving it right clicking on it and select schedule package.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[noonan59]

thanks, I'll check it out. Can I call this ftp task from my application and pass in the web server name and file location from my web program? I don't want to schedule the package on an regular interval, but instead I would like my web program to somehow call this task and execute the package right after it loads a text file to the web server. I have to do this because I need to work with the new table in my web program's execution as soon as it's saved to sql.