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?