execute SSIS or DTS package asynchronously from ASP.NET

Question

I need to load and execute SSIS or DTS packages ASYNCHRONOUSLY from ASP.NET C# page on click of a button and report the success or failure at the end of the execution and if it fails the details of the exception should be shown to the user. it needs to be asynchronous because job could take a long time to finish. user should also be able to cancel the execution of the package while it is running if he wishes to do so. there is also requirement to execute multiple packages in parallel and track the progress for each of them. is this possible to achieve OR too complicated to do it from ASP.NET? Please let me know if you have any sample code or links that do this. Thanks in advance.

Answer 1

A solution you might consider is to use SQLServer jobs. You can set up the packages as jobs with or without a run schedule. You can start, cancel, get job status and get failure information by running stored procedures and queries in the msdb system database. You can run the procedures and queries from you ASP.NET C# page.

Running SSIS/DTS packages in jobs is a common approach. And, this solution would allow you to use Management Studio to manage the jobs as well.

Answer 2

I solved it by queuing up the user requests for DTS/SSIS package execution in a table and reading them in a windows service and executing them in parallel by spawning threads using ThreadPool class. it is working fine. thanks. the drawback of this approach is polling, to make it event driven, creating a WCF service and hosting it in a windows service would be best which I will try next. thanks for all your replies. btw, I can't use sql server agent, it is not allowed as per my company IT policy.