Import text file to sql from web server in real time

[noonan59]

I posted this question in the SQL Server forum also, but I'd like to get ideas from this forum too.

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 the data 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? I'm sure I'll need to set up a DTS in sql, but the trick will be passing in the web server name and file location/name from my web app so the DTS knows where to look for the file. Can I do this through a stored procedure? Or is there another way to make it work?

Thanks for any advice!

 

[ca8msm]

If you are going to go with the DTS solution then I think the answers you get from the SQL Server forum will end up being more beneficial.

If you reversed the process then we may be able to help. For example, rather than:

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

You could read the contents of the text file and insert the values directly into the database table.

Also, you won't have to worry about the DTS package having to know the server location as each server could presumably load the data into the same SQL Server database (if not you still will know which SQL Server database you have used).

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[noonan59]

I think you're correct that it would be better to read the file and insert it into the sql server directly, but I have a problem in that the file is a comma delimited text file with valid commas in the data fields. There are also a few other formatting issues that makes reading the text file and separating the fields correctly very difficult in code. I found a way to handle these problems using regular expressions, but it is very, very time consuming because the text file could contain 20,000+ lines with over 100 data columns on each line.

Sql seems to have no trouble separating the fields correctly and in a timely manner. That's why I'm hoping to be able to set up some kind of automatic DTS package execution - so Sql can do the hard stuff.

 

[ca8msm]

In that case I think you'll be better of with the DTS solution and hopefully the SQL Server forum will be of more help than we can be.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.