Can a package executed from code be aborted in code? The Package class has an Execute() method but no Abort() method. Clearly the debugger can stop a package at any point, so it must be possible somehow.
Are you trying to capture an error and then halt? If so, there's always the FireError event that can be thrown.|||
 JeffJohnsonMVPVB wrote: |
| |
| Can a package executed from code be aborted in code? The Package class has an Execute() method but no Abort() method. Clearly the debugger can stop a package at any point, so it must be possible somehow. |
You're right, the debugger can do that but it might be a misnomer to think that that is the same as aborting from within thw package.
I presume that your abort code would be conditional so I would suggest that the best way to achieve what you want is to use conditional precedence constraints. If you need any help with those, feel free to reply.
-Jamie
|||
Okay, I need to expound. I have created a Windows service that executes SSIS packages. Many of these are long-running, perhaps for hours. If an operator realizes that something is wrong after submitting a package, I'd like to allow him to send a cancel request to the service and then have the service stop the package. What I do NOT want to do is code every single precedence constraint to check for an "abort" flag (and I'm not sure I'd even know how to set such a flag once the package has started without putting an MSMQ task or the like between each executable--yuck!). Could I simply kill the thread that is executing the package? That seems "dirty," and I was hoping for a cleaner method.
|||
I've taken a look at the application class (http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application_members.aspx) and the package class (http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package_members.aspx) and I don't see anything in there that would help.
I can understand why. An executing SSIS package is meant to be a batch/unattended process - once its running you just leave it to get on with it. I fear killing the process might be the way to go.
If you didn't want to put a conditional precedence constraint onto every executable then another option might be to put the executable that conditionally "kills" the package into the package container's OnPostExecute eventhandler. This *could* potentially be a little bit more "graceful" about it than just killing the process. Might be worth a try.
-Jamie
|||
There is a cleaner method. Call RunningPackage.Stop().|||
If your service is starting processes asynchronously your best bet is track the process id. The service could check periodically if the process had ended and "forget" the process if it had. That would give you a way to track what was currently running as well as select a process to kill.. If you want something intelligent in your logging, just have the service write to whatever you use for logging once the process has gone away. Maybe something like *** Operator Canceled ***|||
| jaegd wrote: |
| |
| There is a cleaner method. Call RunningPackage.Stop(). |
Does this work for file system based packages executed from command line dtexec or otherwise?|||
That looks perfect. I should've scanned the class library reference more thoroughly. I'll try to remember to let everyone know how it works.
(Clearly I don't know how to use the quoting feature, sorry.)
|||
Follow-up question: does anyone know if SSIS raises any particular events when RunningPackage.Stop is called? I could trap these events in my custom tasks and fail them cleanly if so.
|||
Yes the SSIS runtime does signal an event when RunningPackage.Stop() is called. You can get at that event by polling periodically -- call to FireQueryCancel() in a custom task, let's say, at strategic points.
If you don't want to poll, you could wrap the the system variable System::CancelEvent in a SafeHandle and wait for the event to be signalled. That also works.
SSIS stock tasks vary at the frequency which they check if the package's Cancel event has been signalled, but they do check it periodically (no, its not documented the frequency at which the runtime check's the cancel event's state).
|||
RunningPackage.Stop() works on package invoked by any "mechanism", since its the same runtime. That includes command line, BIDS, custom invocation util (whatever you can dream up, e.g. web service, windows service), not to mention in-process or out-of-process package executions via Execute Package tasks.
My previous post really means for SQL 2005.