Issues with running SSIS packages programmatically

views:

answers:

Issues with running SSIS packages programmatically

Hi, 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)

option 1 is not suitable for me, because it may recycle worker process if it consumes memory
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)
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.
option 4&5 will have the 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.

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.

Shiraz Bhaiji 2010-09-02 12:36:30

thanks. but I think using this approach, I can execute only one package at a time, where as if I can start the DTEXEC from WCF servcie (ASP.NET calls WCF service), then I can have multiple packages running in parallel. but not sure about security context it runs under.

RKP 2010-09-02 13:22:00

By default the code will execute in the security context of the application pool. By default this is the ASPNET user.

Shiraz Bhaiji 2010-09-02 14:32:03

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.

StephaneT 2010-09-02 14:03:13

this answer gives me some hope. I am thinking of doing this inside a WCF service (called asynchronously from ASP.NET web page) which will invoke DTEXEC and executes the package. Is this better than using windows service? because I need to be able to run multiple packages in parallel. I guess DTEXEC should use the same context as WCF service. but I also need an ability to kill the DTEXEC process that is running a specific package (by passing package name) if required (if it is running for a long time). not sure how to do that.

RKP 2010-09-02 14:46:17

Where would you host the WCF service? Another alternative would be to use AppFabric and expose a WorkflowService that run the SSIS package with the SSIS library instead of dtexec. In that case, you get monitoring of running packages oob. I still think a windows service is the easiest alternative. This windows service only needs to worry about running the packages. You can then use any other means of scheduling the package: straight from asp, with a wcf service or whatever.

StephaneT 2010-09-03 06:50:40

the problem with windows service option is it can only execute one package at a time. if there are many packages waiting to be served, this could cause delay where as if I go for WCF service, I am guessing I can make multiple asynchronous requests to WCF service from ASP.NET webpage and the WCF service can start DTEXEC process for each request and that way I can have multiple packages running in parallel avoiding any delays. the WCF service will be hosted in IIS and be configured with an account having privileges to run the packages (assuming DTEXEC inherits security context, not sure).

RKP 2010-09-03 11:24:29

The windows service can use multiple threads to run packages in parallel. IIRC, I used multiple backgroundworker instances to achieve this, but just using threads should be ok too. Either solution should work, but i'm betting you won't make your life easier with the asynchronous WCF service solution.

StephaneT 2010-09-03 11:29:47

thanks for all replies.

RKP 2010-09-06 08:55:59

ansaurus

tags:

views:

answers:

Issues with running SSIS packages programmatically

related questions