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).
我希望用户通过我们的网站(ASP.NET)上传文件,然后使用SSIS将其导入SQL Server 2005。我们的Web应用程序分为Web服务器(IIS)和数据库服务器(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.
问题是文件上传到Web服务器后,您无法直接从ASP.NET代码调用SSIS包,因为SSIS包只能在安装了SQL Server的服务器上运行。
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.
我已经考虑了各种各样的事情,比如FTP将文件传送到数据库服务器,然后让文件观察者等待它。或者远程调用SSIS包,然后从Web服务器获取文件。但我还没有尝试过任何东西。
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.
我真的很想听听是否有人解决了这个问题以及他们是如何解决这个问题的。我们计划做相当多的事情(即通过导入数据库的网站上传文件),所以我经历了一个可以反复重复的坚如磐石的过程。
2 个解决方案
#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.
只要包具有可解析的文件路径并且SQL Server进程具有必要的权限,该文件就不必与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.
我们为主要应用程序执行此操作,但它不是基于Web的。任何可用的指定机器都可以包含传入文件,然后我们的SSIS包从数据库中查找文件位置信息。包可以在调用时将其作为参数接收 - 我们测试了它。
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).
我个人不知道在这种情况下使用您的网络服务器作为文件主机的可能性(我所有的工作都是在内部托管的应用程序上)。
#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.
它可能不适合您的部署,但可以在Web服务器上独立于SQL Server服务独立安装SSIS服务。我相信独立的SSIS安装需要自己的SQL许可证。
Alternatively, although it doesn't directly mirror your situation, this thread looks like it may have some relevant suggestions.
或者,虽然它不直接反映您的情况,但此线程看起来可能有一些相关的建议。
#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.
只要包具有可解析的文件路径并且SQL Server进程具有必要的权限,该文件就不必与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.
我们为主要应用程序执行此操作,但它不是基于Web的。任何可用的指定机器都可以包含传入文件,然后我们的SSIS包从数据库中查找文件位置信息。包可以在调用时将其作为参数接收 - 我们测试了它。
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).
我个人不知道在这种情况下使用您的网络服务器作为文件主机的可能性(我所有的工作都是在内部托管的应用程序上)。
#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.
它可能不适合您的部署,但可以在Web服务器上独立于SQL Server服务独立安装SSIS服务。我相信独立的SSIS安装需要自己的SQL许可证。
Alternatively, although it doesn't directly mirror your situation, this thread looks like it may have some relevant suggestions.
或者,虽然它不直接反映您的情况,但此线程看起来可能有一些相关的建议。