none
SQL task is hanging

    Question

  • SSIS package contains SQL task to log information into database. Similar SQL task is written under onError event for each control flow component. Internally, it executes a stored procedure. Now, SQL task works fine and update the log table if there is no exception in SSIS package.  However, for any exception(due to wrong input filenae etc), control goes to event handler and tries to run SQL task(written under onError event) and never finishes the task. This SQL task turns to yellow forever.

    Could you please suggest what am I doing wrong? I want to finish the SQL task and complete the package.

    Tuesday, July 30, 2013 7:57 AM

All replies

  • Is there any locking going on in the database?

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 30, 2013 8:04 AM
  • There is no locking. Below query does not retun aything from DB while SQL task is yeloow.

    select cmd,* from sys.sysprocesses
    where blocked > 0

    Also, I have checked the SQL profiler. It doe snot look like the request is goign to DB at all.


    Tuesday, July 30, 2013 8:08 AM
  • How to identify blocking in SQL Server 2005 and 2008

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 30, 2013 8:10 AM
  • I confirm, there is no blocking.
    Tuesday, July 30, 2013 8:18 AM
  • What are you doing inside the SQL Task?

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 30, 2013 8:18 AM
  • I've had, and still have, a few 'inexplicable' problems with OnError event handlers. What version of SSIS are you using? Could you also please share the query and screenshot of the package?

    Kind regards M

    Tuesday, July 30, 2013 8:19 AM
  • SP does a simple table update by taking two input parameters. Same SP executes successfully in SSMS and Control flow but not in Event handler under orError event.
    Tuesday, July 30, 2013 8:21 AM
  • I am using SQL server 2008 R2 version.
    Tuesday, July 30, 2013 8:21 AM
  • The statement and a screenshot of the package will also be of great assistance to help you.

    Kind regards M

    Tuesday, July 30, 2013 8:24 AM
  • Problem is not just with SQL task. When I replaced it with a simple Script task to display error message, Script task truns yellow and hangs.

    Tuesday, July 30, 2013 8:26 AM
  • Do you have one error handler associated with each task in the control flow? Have you written an event handler for each task in the control flow? Does your package actually fail? Does you package actually complete?

    Kind regards M

    Tuesday, July 30, 2013 8:38 AM
  • Do you have one error handler associated with each task in the control flow? Have you written an event handler for each task in the control flow? Does your package actually fail? Does you package actually complete?

    Kind regards M

    Yes, I have written a script task under onError event for the control flow.

    No, package does not fail. It hangs on the component written under onError event. I want to fail the package and execute the task under onErrorEvent to log the error message.

    Tuesday, July 30, 2013 8:45 AM
  • Can try with debugger if you have script task please

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 8:48 AM
  • Hi,

    in control flow if you are using multiple components, then check for their "TransactionOption" property if it is "Required" try changing it to "Supported"

    This will work.

    this property forces the nested components to execution completion.

    Tuesday, July 30, 2013 8:57 AM
  • Yes, I checked in debug mode. Script task fails while reading a file (as the file path is incorrect). Control goes to catch block and task fails. At hte same time it triggers OnError event, but the script task written under onerror turns yellow and control does not go inside.
    Tuesday, July 30, 2013 8:59 AM
  • Are you saying that this task is still yellow -  i.e., hanging -  after all tasks in the control flow have executed successfully?

    Kind regards M

    Tuesday, July 30, 2013 9:00 AM
  • Looks like, problem with input parameter int he SQL task. Could you please tell me what whould be correct data type in the SQL task for a Int64 variable? When  I pass a different variable my SQL task is working now under onError event,


    Can I access User variable within any task under OnError event handler?
    Tuesday, July 30, 2013 9:25 AM
  • Try using a LONG.

    Kind regards M

    Tuesday, July 30, 2013 9:39 AM