Hi all, I have a package that uses BCP to generate interface files for an external client I have a custom logging table that logs the extract info like parameters used, start time, end time, rows, extract status etc I have a stored proc that executes at the start to insert the details into the logging table I have a success constrained stored proc that executes after the BCP process completes and updates the logging table row with the relevant details I also have an on fail task that updates the logging table row with 'failed' for that BCP extract The problem I have is that stopping the package while its doing the BCP generation isn't recognised as a failure. So the on fail task doesnt run and the logging table has the extract set as 'generating' (since the on success doesn't run either) Is there a way I trigger a failure if the package is stopped while a particular task is being executed? The other thing is that the stop sends a ctrl+c (im guessing) to BCP... and that can take anything from 1 sec to an hr to be recognised. Is there a way to cause a hard quit of the process? Trying to regen a stopped extract usually fails because the output file is still present and locked by the quitting bcp process. It would probably be better to use a systime derived temp filenames for this but this decision occurred before i joined the project. I'll probably do it eventually but I want to fix up the unfailed extract log and orphaned process(es) first.. Thanks

I would use System Variable - Cancel Event, overview is here http://msdn.microsoft.com/en-us/library/ms141788.aspx to signal the user cancellation of the package execution. Regarding the BCP I would keep the batch size to the minimum to increase responsiveness. Arthur My Blog

You mean have my 'log fail' sql task run on the event handler tab when the OnQueryCancel event occurs? I cant seem to get it to work... edit: figured it out, i didn't have it running against the correct container :x And good idea about the batch size but I dont think this'll work for us. This is a generic tool that generates about 50 or so interface files ranging from a handful of rows to a 1mil+, and the 10 row ones can take 10 times as long to run as the 1mil+ ones (summed/aggregated data vs straight dump).

Ok, ive done some more testing and it seems to work TOO well OnQueryCancel executes the stored proc 90 times for each extract!! even when no stop occurs. It's flagging all the extracts as failed.

I'm not much of a c# coder so Ithink this is beyond me I set up a trial package with a container that holds a script task with a Wait(10sec) call I added a script task to the onquerycancel event handler for all 3 objects (package/container/script) that records the cancel and cancelevent values. I run the package and stop it, but neither of the values changes (they're all polled multiple times too). Am i missing something? here's my log SSIS package "Package.dtsx" starting. Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: container Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: Script Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: container Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: container Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 Information: 0x63 at Script Task, test: package Cancel = False CancelEvent = 5312 The program '[4556] Package.dtsx: DTS' has exited with code 0 (0x0). this is after letting it run for 3-4 sec The cancel event value isn't consistent either. It's varies between 0, 5300, 5328, 5312 etc (it remains the same for the duration of the package however)