How to import a file when SSIS is on another server
I want users to download a file through our website (ASP.NET), which is then imported into SQL Server 2005 using SSIS. Our web application is split into a web server (IIS) and a database server (SQL Server).
The problem is that after the file is uploaded to the web server, you cannot directly call the SSIS package from the ASP.NET code, because the SSIS package will only run on the server that has SQL Server installed.
I've looked at all sorts of things like FTP'ing a file to a database server and then waiting to view the files. Or, simply remove the SSIS package, which then fetches the file from the web server. But I haven't tried anything yet.
I'd love to hear if anyone has resolved this issue and how they did it. We plan on doing this a bit (like uploading files via a website that is imported into a database), so I get a solid, solid, process that can be repeated over and over.
a source to share
As long as the package has a resolvable file path and the SQL Server process has the required permissions, the file should not be placed in SQL Server.
We do this for our main application, although it is not based on web interfaces. Any available designated computer can contain an incoming file, then our SSIS package looks for file location information from the database. The package could receive it as a parameter when called - we tested this.
I personally don't know about the possibility of using your webserver as a file host in this situation (all my work was on internal applications).
a source to share
This may not be suitable for deployment, but you can install a stand-alone SSIS service on your web server independently of the SQL Server service. I believe a standalone SSIS installation requires its own SQL license.
Alternatively, while this does not directly reflect your situation, this thread looks like it might have some relevant suggestions.
a source to share