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

Question

Post reply

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

vandana_j79

Mr or Mrs. 500

Points: 536
More actions
September 26, 2012 at 12:50 pm

#262213

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

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

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

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 1

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Check the error log.

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

Obs SSC Eights! Points: 997 More actions · Answer 3

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.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

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 ?

vandana_j79 Mr or Mrs. 500 Points: 536 More actions · Answer 6

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?

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

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.

vandana_j79 Mr or Mrs. 500 Points: 536 More actions · Answer 8

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 9

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.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 10

... 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.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 12

Thanks Kraig for the explanation..

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

SSVN SSC Rookie Points: 32 More actions · Answer 13

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.