How to import a file when SSIS is on another server

Question

I want users to upload a file via our website (ASP.NET), which then gets imported into SQL Server 2005 using SSIS. Our web app is separated into a web server (IIS) and a database server (SQL Server).

The problem is that after the file has uploaded to the web server, you cannot directly call the SSIS package from the ASP.NET code, because an SSIS package will only run on a server that has SQL Server installed on it.

I've considered all sorts of things like FTP'ing the file to the database server and then having file watcher wait for it. Or calling the SSIS package remotedly which then fetches the file from the web server. But I haven't tried anything yet.

I would really like to hear if anyone has solved this problem and how they did it. We plan to do quite a bit of this (i.e. uploading files through the website that get imported into the database), so I'm after a rock-solid process that can be repeated over and over.

Answer 1

As long as the package has a resolvable path to the file and the SQL Server process has necessary permissions, the file does not have to be colocated with your SQL Server.

We do this for our primary application, though it is not web-based. Any available designated machine can contain the incoming file, then our SSIS package looks up the file location information from the database. The package could receive it as a parameter at invocation - we tested that.

I personally don't know about the viabilty of using your webserver as the file host in this situation (all my work has been on internally hosted apps).

Answer 2

It may not be appropriate for your deployment, but it is be possible to install the SSIS service stand-alone, independent of the SQL Server service, on you web server. I believe a stand-alone SSIS installation requires its own SQL license.

Alternatively, although it doesn't directly mirror your situation, this thread looks like it may have some relevant suggestions.