I'm attempting to take a BIDS solution that was created on a developer's PC using SQL Server 2005 and get it running on a server running SQL Server 2008 R2. I receive this error every step of the way:
Error loading XXXXPackage.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". 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.
The developer who wrote the solution is no longer here. There is only one developer who can get the solution to run on her PC in debug mode, and we are no longer certain what was done to get it to run there.
I've gone through the archives here, and I've attempted to change the protectionlevel, the password, etc., but I seem to be stuck in an endless loop. If I copy the folders from the 'working' developers PC to the server and open the solution it wants to upgrade the package. The upgrade fails with the same error in all but 3 of the packages in the solution. When I then attempt to edit any of the packages I receive an error that the package may be corrupts.
If I go to the PC where the package was developed and attempt to save a copy of each package to the server changing the protection level at the same time, I receive an error that it could not find msdb.dbo.sp_dts_listpackages." And to be honest, even if this worked, I wouldn't know how to tie all these packages back into a usable solution on the server.
Not having had a great deal of SSIS experience, I'd appreciate it if someone could point me to some step-by-step instructions on how to migrate this package and get it running on the server.
Or if that is not possible, is there a good way for me to recreate the package on the server using pieces of the original? The developer worked for many weeks on this solution, and I don't have that kind of time.