This package has ran fine for 2 months. Now it is doing this error thing. I've read so many articles I'm blue in the face and none of them helped me. I want to know specifically what permission losses would cause this error? Changing protection levels doesn help. Running no protection with passwords in a config table doesn't work. Nothing works. No matter what changes I make, the same error occurs. This is frustrating. Don't want to read anymore articles about anything. I am looking for a smart savvy person who understands where these passwords are stored, registry?, a file?, a folder?, a top secret device? What the heck is going on?? SQL Server Agent runs the job that starts the package. Can't change that. What kind of anamoly would start causing these errors to just start up? Changes in OS permissions? Changes in REGISTRY permissions? Changes in....???? Thank you much. Frustrated. Tired of fighting this. SSIS needs improvements. Executed as user: LSTR\SQLSERVERAGENT. ....4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:00:11 AM Error: 2010-10-10 04:00:12.29 Code: 0xC0016016 Source: 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 Error: 2010-10-10 04:00:12.32 Code: 0xC0016016 Source: 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 Error: 2010-10-10 04:00:12.37 Code: 0xC0016016 Source: Description: Faile... The package executed successf... The step succeeded.Scrambling I.T. Guy

Ok... this points to your package protection level causing issues, as when the package is opening it is failing to decrypt some content within the package as the Password or User key is invalid. It's not registry or OS, it's internal to SSIS. So, question #1: What is the value of the ProtectionLevel property on your package?James Beresford @ www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex

This might not entirely be becuase of the Package Protection level property. We have faced similar issues when we have bad data coming in for processing. Essentially, the source in my dataflow task was a storedproc call, and the destination was a SQL 2005 table. If that is the case, try running a INSERT INTO <destination table/copy of it> EXEC <stored proc name/source query> command. That way, if you really have bad data which is not making it through to the destination table, you'll get the detailed error right there and you can fix the bad data if so. The error message that SSIS package generates in this case is pretty misleading (surprisingly) and I find it frustrating too. Hope this helps. Cheers!! Muqadder.

Muqadder, don't have any SP calls in this package but it is good to know I am not alone. I guess I'll just keep troubleshooting until I'm lucky enough to find the problem, then post it here for all. I see I didn't mention in my original message that all was running fine, the problem occurred after I redeployed the SSIS package because I added one task. Hasn't worked since. It runs in VS/BI but gets that error when deployed to production. All I did was add a task similar to another already in the packgae that makes a connection to an IBM machine. Didn't change the current protection level. Left it as is, set to protect sensative with password, as we do with most the stuff. Nothing really changed. Added one task, redeployed, BANG !!!! The error message listed in original message. Gotta move on to other tasks and come back to this important failing job. No rest for the insane. LOL. Scrambling I.T. Guy

What is the selected job step type (SQL Job Agent) for the package you are executing ?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

SQL Server Integration Services PackageScrambling I.T. Guy WILD WILD WEST SHOW

SQL Server Integration Services Package Scrambling I.T. Guy WILD WILD WEST SHOW This is the issue. You have to select OS CmdExec job step type instead and include /DE <password> to the command line. Also make sure you prefix the command line with DTEXEC. The "SQL Server Integration Services Package" option is buggy and when you specify the decryption password parameter on the command line it is lost.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Okay. I will try that. As FYI, I've always used the GUI thing and am not familiar with command prompt SSIS. Could you give me an example command with a password in it? I'm not shy. That's what the forums are for. We all don't know it all. Thanks CozyRocScrambling I.T. Guy WILD WILD WEST SHOW

Okay. I will try that. As FYI, I've always used the GUI thing and am not familiar with command prompt SSIS. Could you give me an example command with a password in it? I'm not shy. That's what the forums are for. We all don't know it all. Thanks CozyRoc Scrambling I.T. Guy WILD WILD WEST SHOW I have included sample of the option above. You can read more about the different options here .SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

CozyRoc, I did this: dtexec /SQL packagename /CHECKPOINTING OFF /REPORTING E /DE password same error. I checked my job history log and see a connection error for one SQL SERVER connection then I see the error listed in my first thread. I assume the errors are not in real order so the first error about my SQL Server connector may be related to the decryption error. Scrambling I.T. Guy WILD WILD WEST SHOW

Did you change job type to OS CmdExec ?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Yes I did CozyRoc. OS CmdExec YesScrambling I.T. Guy WILD WILD WEST SHOW

Yes I did CozyRoc. OS CmdExec Yes Scrambling I.T. Guy WILD WILD WEST SHOW Okay. Now what is the package protection level that you use?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

ProtectSensitiveWithPasswordScrambling I.T. Guy WILD WILD WEST SHOW

I'm sorry, I meant EncryptSensativeWithPasswordScrambling I.T. Guy WILD WILD WEST SHOW

I'm sorry, I meant EncryptSensativeWithPassword Scrambling I.T. Guy WILD WILD WEST SHOW Can you execute the package from the command line? Include all options you have specified in the job step.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

CozyRoc, how odd. The job ran succefully from the command prompt. I am stumped.Scrambling I.T. Guy WILD WILD WEST SHOW

What I meant to say is it's running successfully and almost done and that was running ot from the command prompt. That's confusing.Scrambling I.T. Guy WILD WILD WEST SHOW

What I meant to say is it's running successfully and almost done and that was running ot from the command prompt. That's confusing. Scrambling I.T. Guy WILD WILD WEST SHOW When you open the job step configuration tab with the command line, is the /DE option and password still there?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

You may have been facing an issue that can be solved by applying what is written in http://biblog.pl/?p=94Arthur My Blog

CozyRoc, not sure what you mean. I ran from the command prompt like you said. The package ran fine and completed. Now, when I open the job that runs the SSIS package which is right now set to OS CmdExec, I see no config tab. So, I set the job back to run as SSIS and the config tab is empty. Did you mean something else?Scrambling I.T. Guy WILD WILD WEST SHOW

CozyRoc, not sure what you mean. I ran from the command prompt like you said. The package ran fine and completed. Now, when I open the job that runs the SSIS package which is right now set to OS CmdExec, I see no config tab. So, I set the job back to run as SSIS and the config tab is empty. Did you mean something else? Scrambling I.T. Guy WILD WILD WEST SHOW I'm sorry. There shouldn't be a tab. I didn't have the job setup dialog in front of me. So when you open the job step type, does the /DE option and password show there?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

No, because when I changed the package back to SSIS instead of OS CmdExec, it wiped it clean. I guess I should startover and test again?Scrambling I.T. Guy WILD WILD WEST SHOW

No, because when I changed the package back to SSIS instead of OS CmdExec, it wiped it clean. I guess I should startover and test again? Scrambling I.T. Guy WILD WILD WEST SHOW Yes, Set to OS CmdExec. Set the required options in the command line. Then press OK to close the dialog. Then press 'Edit' and see if the needed options are still in place.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Yep, followed you instructions. Pressed EDIT and command line looks the same. All there.Scrambling I.T. Guy WILD WILD WEST SHOW

Yep, followed you instructions. Pressed EDIT and command line looks the same. All there. Scrambling I.T. Guy WILD WILD WEST SHOW I'm confused just like yourself. There must be something related to what user account is being used for the package execution because the SQL Job Agent uses different account compared to what you use when running from the command line. But on the other hand it shouldn't matter what account you use because you use EncryptSensitiveWithPassword option and then password option is correctly set. Is it possible for you to create a very simple package , like containing "Execute SQL Task" and which requires SQL Connection requiring password. Then try to setup this test package under SQL Job Agent and see if it still fails. Btw do you remember installing SP or some other fix recently on your server? This might have some implications on your recent troubles.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Request 1 - Make simple package? I created a simple SSIS package with a SQL Task that uses a SQL account and password to log in. A SELECT statement runs and is set to return no results. The protection level is set to EncryptSensativeWithPassword. The package runs fine under the BI/VS environment. Deployed the package to the SQL Server 2005 machine. Created an SQL job to run it. Right clicked on the job and chose START. The job failed with the same error as mentioned at the beginning of this thread. Request 2 - Any service pack changes recently that I am aware of? No. There are some OS patches pushed down but that was after this problem started. More - FYI, INFO 1. Our SQL Service Agent account is a domain account 2. Not sure how group policies are handled at our org if it matters. 3. I know we have some strange registry restrictions but if SSIS stuff is contained in the package and not outside, I guess that doesn't matter. RECAP - To help you out since you've helped me 1. We have proved this: a. My package runs fine from the BI/VS environment. b. My package runs fine if I do the run from a DOS window (Command Prompt) dtexec c. We know that nothing runs from the SSIS package when started by the SQL job d. The simple package requested to be made ran fine in the BI/VS environment e. The simple package requested to be made failed when SQL job tried to start it. f. I am using, EncryptSensativeWithPassword I sure appreciate all your help with this. I think we see a pattern here so...I'm not sure where to go with this but hope you have further ideas. Scrambling I.T. Guy WILD WILD WEST SHOW

The issue may be related to the account that was used to create the job that runs the package. Was the job created again when you deployed the package into production , if yes, then that explains why it was working earlier and not after the changes. The error that gets reported may sound really strange but the actual issue may be the job creation. Try creating the job using any sysadmin previlages account and then try running the package.

When the SSIS package was redeployed, a new SQL job was not created. The SQL job was created originally with an account with 'SA' role. That would be my account.Scrambling I.T. Guy WILD WILD WEST SHOW

My thinking: if the package is configurable try moving the location of the config file elsewhere, see what is going on. You may need to find out what happened in between the package stopped working, e.g. if it a clustered environment then may be some chnages were introduced or patches applied, etc.Arthur My Blog

CozyRoc and ALL, here is my solution to my problem with some critical thinking comments before I give the solution: 1. My SSIS job was running fine until I redeployed the job with a new task added. 2. All the SSIS jobs I have running on a system run fine except the one that started crashing after redeployment. 3. None of the SQL Agent Jobs running SSIS packages, run on a proxy account My solution??? I made a proxy account using SQL VS and set the proxy account as RUN AS in the SQL JOB. My troubles are over. The bothersome thing for me, and will haunt me for a while now is this: Why was all my stuff running fine or still running fime but when I redeployed this one SSIS package, I had to create a proxy account where before it ran fine running as SQL SERVER AGENT. I am lost for words. I watched this video from TECHNET before trying the proxy thing: http://download.microsoft.com/download/9/1/3/913C7A8C-45F3-40BA-9860-8C77F8DDEA34/HDI-TechNet-winvideo-SQLServerAgentConfigure_Complete.wmv CozyRoc, thank you for all the time you spent trying to help me. Especially a couple days ago when you kept responding late into the night. Your info has educated me greatly for future stuff. Thanks. Hope I can do the same for you someday. This thread is over. Thank goodness, but if anybody can explain to me why all things worked before and now I have to use proxy accounts when I redeploy, I'd like to hear from you. TNX everybody. Scrambling I.T. Guy WILD WILD WEST SHOW

Update: The solution presented above worked only for a short amount of time. It suddenly, for no reason, stopped working. The next resolution that solved the issue was this: Removed the Proxy account. Redeployed still using EncryptSensativeWithPassword At deployment step in GUI, after choosing File System, then Package, clicked on Protection Level and chose 'Rely on Server Storage and Roles for Aceess Control' This is insane. Scrambling I.T. Guy WILD WILD WEST SHOW