SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
vandana_j79
vandana_j79
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 86
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
Dennis Post
Dennis Post
SSC Eights!
SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)

Group: General Forum Members
Points: 975 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
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6346 Visits: 3200
Check the error log.

I suspect the login rights. Check the rights of service account for SQL Server Agent.
Obs
Obs
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 424
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
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20475 Visits: 7660
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
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6346 Visits: 3200
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
vandana_j79
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 86
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
Joy Smith San
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6346 Visits: 3200
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
vandana_j79
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 86
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
Antares686
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26770 Visits: 785
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search