SSIS Package Fails When Run with SQL SERVER Agent

Question

SSIS Package Fails When Run with SQL SERVER Agent

forum member

Ten Centuries

Points: 1290
More actions
October 18, 2009 at 7:46 pm

#134520

Hi ,
When i run the ssis package under sql server agent but it runs under integration services->msdb and also in visual studio ide
Please help on how to resolve this
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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

P Jones SSChampion Points: 12352 More actions · Answer 1

When you run under the agent the package is being run by the agent login. When you run from the IDE or management studio it's from your login. Hence you need the same permissions on both for access to the source and destination. Also if you have saved your packege using the default "encrypt with user key", any other login will have a problem if there is sensitive data saved. e.g. passwords.

If you save to msdb set "rely on server storage for access control" and if you run from the agent check the agent account or on the tabs of the jobstep you have "Run As" where you can change the account it runs under.

hussain27syed Ten Centuries Points: 1191 More actions · Answer 2

i have a very similar problem.

I have to schedule a package in server A. The package uses data from server B. I have access to server B . hence i can run it in manually. However the Sql server agent proxy on server A does not have access to server B ':-D'

hence i am unable to run the package from SQl agent.

Is there a way to run the job with my ID instead of the sql agent ID??:w00t:

I have admin access on server A but only member access on server B

Silverfox SSC-Insane Points: 23150 More actions · Answer 3

If you go into the job step properties, the last option as the bottom is :

Run as user.

use this to specify the account that you want the job step to run as

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

hussain27syed Ten Centuries Points: 1191 More actions · Answer 4

Hi

The run as is a drop down and as i see only the Sql agent ID appears in it :crying:

is there any other place where i can set my ID? appreciate if someone could help ( and post screens shots as well)

forum member Ten Centuries Points: 1290 More actions · Answer 5

Hi,

Thanks for the reply,when i replace the flat file path to old remote location path ,its working but if i replace with the new remote location path it doesnt work under sql server agent

Please suggest on how to resolve this

Thanks

P Jones SSChampion Points: 12352 More actions · Answer 6

You will need to create a proxy - read up on Proxy and Credentials in Books Online or google it.