Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

SQl Agent job fails--SSIS package looses password

FTP task executes ok in BIDS.  Password for FTP server is hard coded in the FTP task .
When I scheduled this package as a job  in SSMS it fails  because the password blanks out . SQL agent errors out with The login request was denied . Any inputs how to keep the password  in FTP within SQL Agent?
0
Nana Hemaa
Asked:
Nana Hemaa
  • 3
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
"When I scheduled this package as a job  in SSMS it fails " - where is the SSIS package? imported in SQL store or as a DTSX file in a folder? I believe you'll need to change PackageProtectionLevel in bids like described here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a1f455fb-c75b-4470-a862-d5d89e9a4da6/ssis-package-does-not-remember-password-ole-db-connection-adonet-connection?forum=sqlintegrationservices
0
 
Nana HemaaAuthor Commented:
Saved SSIs on Local SQl server and as a file.  None works in SQL Agent.  I have already played with link above.  Any other inputs from other experts who has  encountered this issue before please?
0
 
Pawan KumarDatabase ExpertCommented:
Which account is used to run that package ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Nana HemaaAuthor Commented:
domain sql  agent service account used to execute all other SQl  jobs
0
 
lcohanDatabase AnalystCommented:
Can you please post the actual error from the SQL Job history?
0
 
lcohanDatabase AnalystCommented:
Crap....this may be one of those "feature by design" and the save password option is only good for the duration of the BIDS session at least when ProtectionLevel=DontSaveSensitive

The KB below describes few ways to set password at runtime:

https://support.microsoft.com/en-us/kb/918760
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now