My SSIS Package works, but fails as a SSMS job (Error: 0xC0016016)

Question

My SSIS Package works, but fails as a SSMS job (Error: 0xC0016016)

I am posting this question and sharing my solution to this issue as my own question because I didn't see the posed problem match the specific issue I encountered and the answers seemed to be scattered in different forum questions.

Background:

• I have four SSIS packages on SQL Server 2012 that import a table from SQL Server 2008 R2, 2008, or 2005, depending on the specific package. I use a designated sql server login and password for the source database and integrated Windows security for the target database.
• Within SSIS I am able to run each package without a problem.
• To ensure this package ran on a schedule, I set up a SSMS job on the same server as the SSIS package. In Job step properties, I chose: SQL Server Integration Services Package > run as SQL Sever Agent Service Account > Package source: File system).

The Symptom: When manually running the job to make sure it worked, I got an error and saw this in the Log File Viewer. This was the first of several errors, but as this was chronologically the first error, I looked into this one first.

• Error: 2014-10-24 09:52:34.48
• Code: 0xC0016016
• Source: [Redacted -- the correct name of the table I was importing]
• Description: 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.
• End Error

Answer 1

I looked up the error code on Google and started looking at resolutions. Rather than retelling how I found the bits and pieces of the resolution, I am presenting an actionable result in sequence -- at least for me and the network infrastructure I'm working with.

1. In the "properties" panel of the SSIS solution (do this first) and each package in that solution, reset the "ProtectionLevel" attribute to EncryptSensitiveWithPassword and set a password. The package passwords must match the solution password.
2. Just a double-check, run your packages in debug mode to make sure that no other new issues arise. In my case, I needed to re-enter the sql server password for the source server database.
3. Rebuild your SSIS solution.
4. In SSMS, open your job and open the job step properties for the task in question.
5. Select the "Command Line" tab. A "Package Password" popup appears. Enter the password that you entered in step 1.
6. Select "Edit the command line manually" and place the same password from step 1 immediately after /DECRYPT.
7. Repeat steps 4-6 for each job step that runs this type of package.
8. Press the "OK" button and re-run your job.

I was able to run my job successfully after that.