Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ssis package not working with sql server agent but run fine manually Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 12:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 4:33 AM
Points: 10, Visits: 59
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
Post #1364893
Posted Thursday, September 27, 2012 7:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
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.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1365222
Posted Thursday, September 27, 2012 12:45 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 1,499, Visits: 2,517
Check the error log.

I suspect the login rights. Check the rights of service account for SQL Server Agent.
Post #1365452
Posted Thursday, September 27, 2012 12:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 7, 2014 1:46 PM
Points: 247, Visits: 419
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.


Post #1365458
Posted Thursday, September 27, 2012 1:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 6,130, Visits: 7,166
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1365467
Posted Thursday, September 27, 2012 10:15 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 1,499, Visits: 2,517
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 ?

Post #1365592
Posted Thursday, September 27, 2012 11:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 4:33 AM
Points: 10, Visits: 59
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?
Post #1365619
Posted Friday, September 28, 2012 1:17 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 1,499, Visits: 2,517
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.
Post #1365646
Posted Friday, September 28, 2012 2:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 4:33 AM
Points: 10, Visits: 59
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.
Post #1365676
Posted Friday, September 28, 2012 7:27 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734
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.


Post #1365861
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse