Post reply

Execute SQL task not failing

  • September 20, 2010 at 9:54 am

    #140670

    I've been banging my head around this for a few days now. I have an SSIS package that manipulates some files and then processes them via an execute SQL task then move the files using a script task. My issue is that sometimes (I haven't found a pattern yet) the SQL task will not complete entirely. As the second to last step of the sql code within the task is to write the filename processed to a table. For some reason an entry isn't showing up. More puzzling is that the package (more specifically, the job) isn't showing that it failed.

    The precedent constraints are all showing green which means that they should return with success. Am I missing something? To add some variety, it also doesn't happen every day. The only way I know it didn't work is if I don't get an email. That's the last step of the process.

    Any help would be greatly appreciated.

  • October 14, 2010 at 10:07 pm

    #1237291

    Getting close to figuring this out. Turns out when you use an event handler you need to explicitly change the property of 'FailPackageOnError' or 'FailParentOnError' to true. With it set to false and no event handler, if there is a failure SQL Server will return it as a failure. However, if you have set to false and use an event handler, if there is a failure SQL Server will NOT see the error. I was able to confirm this by creating a job and running with the different properties.

    It's not a bug as it's doing what's it's supposed to, but I wish the default for those to properties would be set to true. What I consider somewhat of a bug is the fact that the error will bubble up to SQL Server when run as a job if no event handler is present and the 'FailPackageOnError' is set to false.

    Now that I'll be able to see what the error is I should be able to fix the root of the problem. My guess is that there is a deadlock.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply