Job and package execution account

Question

Post reply

Job and package execution account

klineandking

SSCrazy

Points: 2570
More actions
September 12, 2010 at 5:54 pm

#141968

good afternoon guys, i have a package that i want to set up as job;my question is, does the package need to have the same account with the job that it will run on, and also if i have to run the job under a proxy account what kind of privileges does the proxy need to have for the package to run well.
i would also like to know how to create a proxy account for the package

Viewing 10 posts - 1 through 9 (of 9 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

klineandking (9/12/2010)
i would also like to know how to create a proxy account for the package

In SSMS, expand SQL Server Agent, right click on Proxies and select New Proxy...

Before creating a proxy, you should create a Credential. To create a credential expand Security (at server level) and right click on Credentials and select New Credential...

Hope this helps you.

I hope others will answer your other two questions.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

By default, if you set up your package in a SQL Agent job, it will run in the context of the account that is configured with the SQL Agent windows service. Without a proxy, that account would need whatever permissions to database objects or external resources that are accessed by the SSIS package.

When you create the proxy in SMS, you can assign the privileges that it has with SQL Server, one of which is "SSIS Package Execution". Once you grant this privilege, the account will appear in the "Run As" dropdown in the SQL Agent Job step for the package.

Just as with the default SQL Agent account, this proxy account must also possess the the permissions to access the resources used in the SSIS package.

Also, a caveat if accessing network file locations - you should use the UNC path and not a mapped drive, because the default SQL Agent or proxy accounts generally do not have mapped drives configured.

klineandking SSCrazy Points: 2570 More actions · Answer 3

Thank you so much i appreciate the help, please could you explain what is a UNC drive and what is a mappped drive and how i could set up the UNC

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

An example of a UNC path to a remote server would be

\\server1\globaldrive\myfiles

if you had mapped a drive letter to \\server1\globaldrive\ it might be:

G:\myfiles

Your network administrators should be able to help you with this.

klineandking SSCrazy Points: 2570 More actions · Answer 5

Thank you so much, i have one more question for you,the package in question used to execute under 2 hours as a job but now it takes up to 5 hours as a job to execute do you have any ideas on what makes it to take that long and what i could possibly do to shorten the execution of the job

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

Well, not having any knowledge of what your package does, or how it does it, I can only respond in a general fashion....

Scalability issues; is this due database growth? Have proper indexes been defined? Are Statistics being updated?

Hardware issues - what other processes are occurring on the server that might impact this job? Same for network resources, especially during a backup window when there is a lot of data flying around the network.

klineandking SSCrazy Points: 2570 More actions · Answer 7

thanks again,i have a general idea of what the package does it is building a dropping the data warehouse and re building it using batch files

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 8

Another thing to watch is if you have an Antivirus software on the box, it tends grab files unless you have exclusions!

klineandking SSCrazy Points: 2570 More actions · Answer 9

i wanted to thank you for all your wondeful tips on this issue i had, i pray that you have more wisdom to guide fellas like us thank you again