Hi, 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.
+1
A:
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.
bobs
2010-08-12 13:29:35
thanks for the reply, but how does this work? the SSIS/DTS packages are stored on the webserver. do I need to create a job on the webserver? if so, do I need to install SQL components on it to create a job (not sure if the server policy in my company will allow this)? how the stored procedure will know about the job that is located somewhere else?
RKP
2010-08-12 13:55:51
are you sure the SSIS / DTS are on the webserver? They should be part of the DB...
Kris C
2010-08-12 14:16:59
yes, the SSIS/DTS packages are not located on the database, they are stored on the file system on webserver. If I need to create a job and call it using a stored procedure, then do I need to install any SQL components on the webserver? if not, I will have to go for the option in my original question.
RKP
2010-08-12 14:35:49
To run SSIS on the *webserver* you'll need SSIS to be installed on the webserver (and SQL server licence for the web-server). Alternately if the SSIS packages are on the database, with a stored proc on the DB, then they can be called from the webserver with no SQL components on the webserver.
Kris C
2010-08-12 14:48:21
+1 the use of sql agent jobs. Use sp_start_job to launch an existing job, let the package write it's status to a custom table and show that in your aspx page.Alternatively, you could use dtexec.exe to run the package from file in a thread, but that'll also need an installation of SSIS.
StephaneT
2010-09-01 13:54:01
A:
Check the msdn link http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/ff441dc3-b43b-486b-8be1-00126cf53812/
Hope this will help you
Aman Bhullar
2010-08-13 11:42:48
A:
You could use Rhino ETL instead of SSIS. This is a fully-fledged ETL library which allows you to code your ETL jobs in C#.
nocache
2010-08-17 07:41:19