ssis package not working with sql server agent but run fine manually

  • I have setup job which run ssis package. In this job i pick data from oracle server with link server. When i run job manually it complete in 20 mins but when i use sql server agent it just keep hanging.

    Any ideas...

    Thanks

  • Hi,

    Try checking all logins you are using throughout the whole process, including all services accounts.

    In the jobs list, take a look at the history. Maybe that will give more idea on whats going on.

    Goodluck.



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • Check the error log.

    I suspect the login rights. Check the rights of service account for SQL Server Agent.

  • Also, make sure you have the right version of the Oracle client. 64 vs. 32bit makes a huge difference. You should also attempt to build some kind of auditing into your package to help with things like this.

  • vandana_j79 (9/26/2012)


    I have setup job which run ssis package. In this job i pick data from oracle server with link server. When i run job manually it complete in 20 mins but when i use sql server agent it just keep hanging.

    Any ideas...

    Thanks

    Dealing with an oracle connection is a bunch of possible issues. What are the error message(s) you're getting? It could be a bad setup of the .ora files on the server, login issues, any number of things.


    - 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

  • Evil Kraig F (9/27/2012)


    vandana_j79 (9/26/2012)


    I have setup job which run ssis package. In this job i pick data from oracle server with link server. When i run job manually it complete in 20 mins but when i use sql server agent it just keep hanging.

    Any ideas...

    Thanks

    Dealing with an oracle connection is a bunch of possible issues. What are the error message(s) you're getting? It could be a bad setup of the .ora files on the server, login issues, any number of things.

    Kraig, Small doubt. If there are problems with Oracle setup it shouldn't have worked when manually executed, is it ?

  • Thanks for the reply.

    Yes, package running fine when i run manually only sql server agent is issue. The thing is it was working fine till last friday our server re-started on friday & it just stop working. I checked all the logins & error log. No error msg because job doesn't fail. whenever i open job history it shows still running.

    Is it possible that server re-start cause the issue or look like permission issues but problem is i am not a DBA so not sure how should i check these all?

  • Any other process runs at that time ?

    How many records are fetched from oracle approx. ?

    What about linked server settings ? Check the security tab in linked server properties and see the credentials used to connect to oracle.

  • Yes other process run same time.

    It fetch arround 1 milion records

    Connecting to link server is by a user & user has admin rights. Thing is same package works fine when we run manually.

  • It has been a while since I had to deal with this myself, but as I recall if you need to run the process under an account with logon local windows permission granted in policies. If you are running under a system account or Agent account you don't want to have that right, then look at using a proxy account for Agent to execute the job under.

  • ... Just curious..

    Can you login with the service account of SQL Agent and execute the job manually and see if it works ?.

    If it works, think changing the schedule time may be useful.

    Also not sure if the other processes are having any effect on this or not.

    you can try by rebooting as well.

  • Joy Smith San (9/27/2012)


    Evil Kraig F (9/27/2012)


    vandana_j79 (9/26/2012)


    I have setup job which run ssis package. In this job i pick data from oracle server with link server. When i run job manually it complete in 20 mins but when i use sql server agent it just keep hanging.

    Any ideas...

    Thanks

    Dealing with an oracle connection is a bunch of possible issues. What are the error message(s) you're getting? It could be a bad setup of the .ora files on the server, login issues, any number of things.

    Kraig, Small doubt. If there are problems with Oracle setup it shouldn't have worked when manually executed, is it ?

    Manually executed runs off your system files if you're firing it up from Visual Studio. SQL Agent will use all local files on the server. Unless you meant rt-click - Run from the SSIS server, in which case I'd have to do a little research to figure out which fileset it'll use.


    - 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

  • Thanks Kraig for the explanation..

    .. I was under the impression that she right clicked on the job under SQL Agent and executed it manually...

  • We had a similar issue and I did this to get it to work. If you check the job History, you would be able to see if your package runs as 64 bit or 32 bit version ( the second line would show the version and bit). In our case it was calling the 64 bit version when running from the SQLAgent job. So in our job, we had to explicitly call the 32 bit version using the xp_cmdshell. The 32 bit version is usually located at

    "c:\Program Files(x86)\Microsoft SQl Server\90\Binn\DTS\Binn\DTEXEC.exe".

    Also make sure the user under which the SQL Agent job runs has access to this folder. One other tip is if you need the short name for the path use dir /x to find it.

Viewing 14 posts - 1 through 13 (of 13 total)

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