Post reply

SSIS package hang and run forever

  • November 21, 2011 at 8:09 am

    #260451

    Hi,

    I have a problem with a SSIS package that used to works fine for years. Since last week, it hang and run forever.

    The package runs at every minute and calls a StoredProc that connect to a Linked Server (Oracle database), get data and insert new rows in Oracle.

    When executing the StoredProc manually, it works perfectly. But when it is called from a SSIS Package, or a Agent job calling directly the SP, it always hang. When I try to kill the job, its task state is "running" and command is "killed/rollback" in Activity Monitor. I have to kill the Agent service to try to re-run the job.

    I have applied last Service Pack and cumulatives updates

    Now, I'm not a DBA. Just try to do my best, but for this, I'm totally lost. Can anyboby help me on that matter, please?

    SQL Version is:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1797.0 (Intel X86)

    Jun 1 2011 15:33:53

    Copyright (c) Microsoft Corporation

    Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

    thanks a lot for your time and help

  • November 21, 2011 at 8:16 am

    #1410746

    If you're allowed to modify the package, you could enable logging and see where it's up to when it hangs. Otherwise, you'll probably need to keep a close eye on activity on all the servers that it touches, so that you can find out which command is causing it to stop.

    John

  • November 21, 2011 at 11:52 am

    #1410972

    It might be a permissions issue. Check to see what permissions the SSIS package executes with and compare that to the permissions that are used when running it manually.

  • November 22, 2011 at 11:48 am

    #1411730

    thanks for the replies

    When I execute the package from the SSIS server (by doing a Run Package) it works fine. Does running a package this way also involve the SQL Agent?

    As soon as I start the task from SQL Agent, it hang and runs forever, when the exact same configuration worked for years! Nothing has changed...

    I forwarded the log into a SQL table, but it does not says much. last message is: ExternalRequest_pre: The object is ready to make the following external request: 'ICommand::Execute'.

    SQL Agent runs with LocalSystem account. The is 10 other SSIS packages who works fine, althought no other involve a Linked Server.

  • November 23, 2011 at 9:23 am

    #1412308

    Dominic Gagné (11/22/2011)

    thanks for the replies

    When I execute the package from the SSIS server (by doing a Run Package) it works fine. Does running a package this way also involve the SQL Agent?

    No, that uses your permissions, not the Agent's login (or if the job's set to a proxy user, it'll use that instead of agent).

    As soon as I start the task from SQL Agent, it hang and runs forever, when the exact same configuration worked for years! Nothing has changed...

    That usually screams a security problem, I'll agree with that. I'd probably also review the job step to make sure noone accidentally monkeyed with it while using it as a template for something else or something similar.

    SQL Agent runs with LocalSystem account. The is 10 other SSIS packages who works fine, althought no other involve a Linked Server.

    Ah hah! Maybe. Review the linked server impersonation security and make sure that it's impersonating for all users. LocalSystem can't use its own security context on another server.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 24, 2011 at 6:15 am

    #1412670

    Actually, it look like it was a security issue.

    I created a new local account with administrative rights and configured the SQL Agent service to log on with this new account, instead of using LocalSystem. This way, it works perfectly.

    Now, what could have happened??! It used to works for years and in a matter of a minute, it stopped working. Oracle DBA told me nothing changed on their side and the problem comes from the SQL box.

    thanks all for your great help and advices

  • November 26, 2011 at 7:55 pm

    #1413147

    Dominic Gagné (11/24/2011)

    Actually, it look like it was a security issue.

    I created a new local account with administrative rights and configured the SQL Agent service to log on with this new account, instead of using LocalSystem. This way, it works perfectly.

    Now, what could have happened??! It used to works for years and in a matter of a minute, it stopped working. Oracle DBA told me nothing changed on their side and the problem comes from the SQL box.

    thanks all for your great help and advices

    It's possible the local .ora files ended up in a locked down directory after policy changes for the servers.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 7 posts - 1 through 6 (of 6 total)

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