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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » ssis package not working with sql server...

14 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

vandana_j79

Posted Wednesday, September 26, 2012 12:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 7:49 AM
Points: 9, Visits: 47

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

Dennis Post

Posted Thursday, September 27, 2012 7:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:26 AM
Points: 109, Visits: 281

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.
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Post #1365222

Joy Smith San

Posted Thursday, September 27, 2012 12:45 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:42 AM
Points: 1,370, Visits: 2,285

Check the error log.

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

Post #1365452

Obs

Posted Thursday, September 27, 2012 12:52 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 05, 2013 2:29 PM
Points: 247, Visits: 392

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

Evil Kraig F

Posted Thursday, September 27, 2012 1:10 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658, Visits: 6,100

vandana_j79 (9/26/2012)

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

Joy Smith San

Posted Thursday, September 27, 2012 10:15 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:42 AM
Points: 1,370, Visits: 2,285

Evil Kraig F (9/27/2012)

vandana_j79 (9/26/2012)

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

Post #1365592

vandana_j79

Posted Thursday, September 27, 2012 11:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 7:49 AM
Points: 9, Visits: 47

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

Joy Smith San

Posted Friday, September 28, 2012 1:17 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:42 AM
Points: 1,370, Visits: 2,285

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

vandana_j79

Posted Friday, September 28, 2012 2:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 7:49 AM
Points: 9, Visits: 47

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

Antares686

Posted Friday, September 28, 2012 7:27 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions