March 7, 2017 at 11:32 am
Hi,
I'm in the process of running some tests using the For Loop container which contains a simple SQL tast within it that queries a non existent table (this is to ensure the task fails). Basically, I want the container to iterate 3 times even when the SQL task generates an error. Sometimes it works but other times both the SQL task and container have the little yellow spinning processing circle on the upper right corner of them and they never stop. I'm just not sure why it is doing this. Note that in the OnError Event handler for both the sql task and container I have set the Propagate system variable to False.
Any help would be appreciated. Thanks!
March 7, 2017 at 11:53 am
There's a much better way to make it fail. Let it select a divisor of 0 and have the query fail for a more practical reason of divide by 0.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 12:09 pm
Ok, thanks Steve. I implemented your suggestion. One thing I neglected to mention is that I have a Script task in the OnError event which displays a messagebox containing ErrorDescription value. This messagebox is only for testing purposes. Anyway, it appears this message box might be what is causing the sporadic "hanging" I mentioned earlier since when I disable the script task (which displays the message box), I don't seem to encounter the hanging issue (not yet anyway!). I'm unclear though why the message box would cause this problem.
March 7, 2017 at 12:25 pm
The problem is that the account under which the job runs has no "screen" to present the message to. Not sure what Windows does with that, or SSIS for that matter, but it's generally a bad idea to leave that kind of code in place for execution in a SQL Agent Job.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 12:34 pm
OK, thanks for the information Steve. I was sensing that the message box was causing the problem. Note that this package won't be running in a SQL Agent job though. Instead, it will be executing by a batch file that is triggered by a scheduled task on a job server. Either way, my plan wasn't to leave the message box in permanently. I was just curious what was being stored in the ErrorDescription variable. Thanks for your insights though.
March 7, 2017 at 1:24 pm
Edding Jan - Tuesday, March 7, 2017 12:34 PMOK, thanks for the information Steve. I was sensing that the message box was causing the problem. Note that this package won't be running in a SQL Agent job though. Instead, it will be executing by a batch file that is triggered by a scheduled task on a job server. Either way, my plan wasn't to leave the message box in permanently. I was just curious what was being stored in the ErrorDescription variable. Thanks for your insights though.
Glad I could help. Assuming that you have your package set up in such a way that you could run it locally in BIDS, you could always temporarily enable the code to see the error, and then disable it and re-build/re-save.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply