Issues with running SSIS packages programmatically

I have spent days trying to solve this problem and still stuck with this and I have posted some questions already on this website, but didn't get satisfactory answers. I am trying to be more clear this time and hope to get a better answer. I have gone through this article already http://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx and here are my issues (I need to run the SSIS package from ASP.NET)

1. option 1 is not suitable for me, because it may recycle worker process if it consumes memory

2. option 2 is also not suitable because of security issues in creating a new process and passing the context to new process looks very complicated for me (according to the support article)

3. option 3 is not suitable because using SQL Server Agent to run SSIS package is not allowed by the company I am working for(I guesss it requires installation of db engine on application server, not sure). but SSIS is installed on the application server.

4. option 4&5 will have the 开发者_JAVA技巧same issues as options 1&2.

I guess the only option left now is to create a windows service and start the service from ASP.NET. but will this allow running multiple packages in parallel? OR is there a better alternate solution for this? please let me know. Thanks.

I have built something like this in the past with

• a windows service that monitors a database table
• an asp.net page that inserts jobs in the database table
• each job is a (set of) ssis package(s)
• the windows service is configured at startup with a number of threads in a pool
• each time the windows service sees a new job, it checks if there is a thread available and launches the thread with the job to be run with dtexec.exe using System.Diagnostics.Process (you can use the SSIS class libraries, but I found dtexec.exe more useful
• the thread (and the job) runs in the security context of the windows service, thus using the windows credentials used by the windows service
• when a job is finished, the windows service updates the database table

You could change this to do without the table, and expose a wcf service from the windows service with asynch methods that return when the job is finished. I'm not sure how to use multiple threads in that case, but I think by making it asynch you also make it inherently multi-threaded.

You are on the right track with the windows service.

Instead of running the SSIS package from asp.net, get asp.net to place an entry in a queue or a list. Then get the windows service to monitor the list.

When the windows service sees that a new item is in the list it runs the SSIS package that the list item refers to.

The benefit of this is that the web page does not have to wait while the SSIS package runs.