We are migrating from a botched 64-bit SQL Server setup to a new 64-bit SQL Server Setup (One instance versus 2). Things that worked on the first server, don't work on the new. My speculation is that the AS400 drivers installation is botched, but asking for feedback.
Am running a scheduled job that simply executes an SSIS package. There is a package configuration for the connection strings to AS400 and SQL Server. The package runs fine when you run it from DTEXECUI (obviously adding configuration file to run). The job is setup exactly the same as on the original server but gets the following errors:
Side question: Why does the job history errors conflict with the SQL Server logging from the package? Very frustrating.
Job History Error (I found these to be very inaccurate in the past):
Executed as user: bfusa\mfgsql. ...rsion 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:45:06 AM Error: 2007-04-11 09:45:06.76 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS
assword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-04-11 09:45:06.78 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS
SQL Server Package Logging:
System.InvalidOperationException: The 'IBMDA400.DataSource.1' provider is not registered on the local machine.
BTW, I tried using a cmdexec step to force it run in 32-bit mode, and still received errors. If it is in fact a driver error, why does it run fine in DTEXECUI? I know that always runs in 32-bit mode, but I tried to force a 32-bit mode also.
This might not be the answer but for that error "The system cannot find the file specified." on 64-bit I've opened the package, recompiled all my scripts, saved and ran again and it fixed it. This seems to happen less often in SP2 (or at least in SP2 you will get a warning in 32-bit mode that it is missing a binary for a script).
Based on the rest of your error message its probably not the correct answer but it can't hurt
Two issues here.
1. 32 bit vs. 64 bit package execution
The job history pasted in the message shows that the job is /was running under 64 bit. But even when that is fixed to run under 32 bit (as you apparently did in a cmdexec step) , the execution would still not initiate because of the Package Protection level property setting, which is the second issue. As to the 32 vs 64 bit package execution via dtexec, here's what is displayed when running the two versions of dtexec.exe on the x64 platform (and you can see that the job history shows a 64 bit execution attempt).
"%ProgramFiles(x86)%\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
At least one of the DTS, SQL, or File options must be specified.
"%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
At least one of the DTS, SQL, or File options must be specified.
That history shows it is running under 64 bit.
2. Package protection level
Even though you modified it to run in 32 bit mode, there are still errors. These have nothing to do with that platform, but rather, the PackageProtectionLevel. This issue is discussed ( with resolutions) in the following article: http://support.microsoft.com/kb/918760
|||A reinstallation of the DB2 drivers was done and this fixed the issue.