I've done about all I know to do....
1. 32-bit vs. 64-bit
Tried changing SQL Agent Job step from SSIS to Operating System and, since dtexec.exe supposedly always runs as a 32-bit version, using dtexec.exe /File "\\share\myfile.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW.
No change...still gets error.
2. Debugging Run64BitRunTime setting
Tried setting to False.
3. File system permissions for SQL Agent Service Account
- Maybe it's because I'm building the package under one account (my NT account) and then running it under another account that can't decrypt the file (SQL Agent Service Account)?
a. Tried using EncryptSensitiveWithPassowrd
, setting a password, and then adding the /Decrypt "mypassword" switch to the dtexec.exe command line.
b. Tried using DontSaveSensitive
security setting, no password on the command line.
5. Just try to validate the package using the /Validate switch
All that I can think to do now is try to re-encrypt
the package while logged in under the SQL Agent Service Account (need to get with our IT group about that). If that doesn't work....grrrrrr