Home » .Net Framework

SQL Server agent job will not retain package password (encrypt sensitive with password)

I have a package protected by a password - I am already unhappy that to get it to use the configuration file to change connection strings for the production servers I have had to hardcode the password into the config file - very insecure!

However, the package now deploys correctly to the production server and will run from there OK, but NOT if scheduled as a SQL Server Agent Job.  Thus is because however often I edit the command line to include the password after the DECRYPT switch (which it has prompted me for when I click on the command line tab), the Job Step will not retain it.

If I open it up after I have edited it and closed it, the password has disappeared.

I know that if I run dtexec plus the code in the Command Line tab (with the password), the package runs OK.

This is driving me insane! 

I have read all the other posts and so I tried replacing the SSIS package step with a CmdExec step and pasting that code into there - then I get an OLEDB error..

The code I use is:

DTEXEC /SQL "\ImportRateMonitoringTables" /SERVER servername /DECRYPT password /CONFIGFILE "D:\Microsoft SQL Server\SSIS\Deployments\RateMonitoringImportTasks\Deployment\ImportRateMonitoringTables_Production.dtsConfig"  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING E

and I get

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF

although the same code executes perfectly from a command prompt.

Please does anyone have any experience with a similar problem and if so, how did you get round it?

Thank you


4 Answers Found


Answer 1


I had the same issue with the getting the package  to run successfully. I have an SSIS package with 'Encrypt sensitive  with password'. I was to able to get it working with the following steps:

1. Connect to your Integration Services instance on the same server  where the package is located.

2. Right click on the MSDB folder and click on the option to "import package".

3. The package location will be file  system, browse to the package name. It should have a dtsx extension. Click on the elipise button next to protection level. Select encrypt  sensitive data with password. Type in your password  for the package. Click ok. Enter your password for the package once more.

4. Under sql  Server Agent right click the jobs folder and select new job. Create a new job  step. The Type will be SQL Server Integration Services Package. The package source will be SSIS Package Store. Select the correct server name. Select the the elipise button next to the Package: option and select the package in step #3 under the MSDB folder.

5. You shouldn't need the configuation file because the password is already stored in Step #3. You should also be able to schedule the job now.

Give it a try and let me know if it works. No more configuation file needed.


Answer 2

You rock, thank you.  I actually went through your instructions once, and it's this part that I was missing:

Click on the elipise button next to protection level. Select encrypt  sensitive data with password. Type in your password  for the package. Click ok. Enter your password for the package  once more.

Answer 3

Thank you
I used this and it is working perfectly


Answer 4


I have a package  with encrypt  sensitive with password. I followed your instructions. It gives me below error with out configuration  file. Any clue ?


Executed as user: XXX\XXXX. Microsoft (R) SQL server  Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:54:57 PM  Error: 2010-07-29 13:54:57.81     Code: 0xC001405F     Source:       Description: Failed to decrypt an encrypted XML node because the password  was not specified or not correct. Package load will attempt to continue without the encrypted information.  End Error  Error: 2010-07-29 13:54:58.08     Code: 0xC001602A     Source: FTP Connection manager "FTP Connection Manager"     Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed  .  End Error  Error: 2010-07-29 13:54:58.09     Code: 0xC002918F     Source: Arello FTP FTP Task     Description: Unable to connect to FTP server using "FTP Connection Manager".  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:54:57 PM  Finished: 1:54:58 PM  Elapsed:  0.343 seconds.  The package execution failed.  The step failed.



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure