Upload a Text file from a Web Client to SQL Server
I have hit a wall!
I am writing a ASP.Net application which should accept text files from Web users and move the data into a SQL Server.
This is what I don't want to do: I know I can stream the data in the text file and do multiple inserts into the table through the application. The volume of data will not make it feasible for such a lot of IO operations.
This is what I want to do:
I upload the file to the Web Server which is outside a fire-wall.
I have a DTS package with a FTP task wich pulls the data from outside the fire-wall to the SQL server (That is where the problem is).
The DTS package does a Text to Table pump task to upload the data.
When the DTS package executes on My Machine or the DBA's or the Network Admins machine, we are able to successfully save the file on the server where the SQL Server is running. This is because of our network authentication.
But If I execute the DTS package from the Web Application, the Application does not have access to the file system of the server and hence the FTP fails. How can I programmatically save the ftp-ied text file from the Web Server through the application?
I presumed that the SQL Agent's access to the file system of the machine is sufficient. But apparently that is not letting the FTP task of the DTS package save the file.
However if the DTS package is a made as job and scheduled. The Auto scheduler seems to have access to the folders to the SQL Agent as it is triggered by the SQL Server itself and not by any user.
Any help is appreciated.
Thanks - Peter