| Author |
Topic |
|
masterdineen
Aged Yak Warrior
550 Posts |
 Posted - 2012-04-04 : 17:05:40
|
| HelloWhen creating Agent Jobs.what is the best practice when creating them.For example. I have an agent job setup to run a ssis package that uploads a file to FTP. I am using protection level EncryptSensitiveWithPasswordWhen i run via BIDS it works fine. But fails via agent job.Should i run the Agent job using a proxy?what other things can i do so this agent job with work?Kind RegardsRob |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-04-04 : 19:21:56
|
| I always choose "rely on server storage and roles"always |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-04 : 20:17:07
|
| is the package stored in same server where integration services is installed?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-04-05 : 04:15:29
|
| what about using a proxywould you always recommend using them? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 13:17:58
|
| yep..thats way to go for running package from job. but password has to be stored while importing package to server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-04-10 : 05:09:05
|
| Hello Visaka16when you say the password has to be stored. Where would it need to be stored? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-04-10 : 11:53:04
|
| If you choose "rely on server storage and roles" then no password needed. But SQL Agent account needs permission on all resources touched by package. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-10 : 16:32:26
|
quote:
Originally posted by masterdineen
Hello Visaka16when you say the password has to be stored. Where would it need to be stored?
while calling it from job step in sql server agent of server you need to store password if its using encrypt using password protection level------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-04-11 : 03:33:25
|
| sorry to be dump.but where abouts in the job setting do i save the password? |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-04-11 : 05:45:00
|
| Ok i have got the package running, But now the FTP part of the package is failing. But when i run from BIDS it works fine.All the package consists of is. Data flow task, ----- FTP Task ---- Two emails task. one for success and the other for failureWhat else can i check.???? Getting frustrating now. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-04-11 : 07:22:37
|
| WEHAY Ive got it working. After a little hunting around.The areas covered to get working areProtection level set to EncryptSensitiveWithPasswordmake sure owner of agent job is same as owner of ssis package.Run agent job via proxy account. with the credientials owner to all three MSDB database rolesSQLAgentUserRoleSQLAgentOperaterRoleSQLAgentReaderRolealso db_dtsadminintegrate the package password within the command line of the step screen on the agent job eg/FILE "\\server\ICTdept\DTS Files\Packages\FTP\FTP\bin\Deployment\package.dtsx" /DECRYPT "password"/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING Ealso this video may give some ideashttp://go.microsoft.com/fwlink/?LinkId=140819&clcid=0x409Thank you all very much for your helpRegardsRob |
|
|
|
|
Agent Job Best Practice