SSIS:刚开始获得“在指定状态下无效的密钥".我安排的SSIS程序包上的错误 [英] SSIS: Just started getting a "Key not valid for use in specified state." error on my scheduled SSIS package

问题描述

我在SQL Server 2005计算机上有2个计划的作业，计划在每天早上(大约2:00 AM)运行.这些工作(大部分)效果很好(大部分时间)，尽管我遇到了一些小问题，但不得不解决这个问题却使我很头疼.

I have 2 scheduled jobs on my SQL Server 2005 machine that are scheduled to run each morning (around 2:00 AM). These jobs have worked fine (mostly) for years and although I've had a few hiccups that I've had to work through this problem is completely stumping me.

两天前，我的一个包裹开始报告以下错误:

Two mornings ago, one of my packages started reporting the following error:

Executed as user: [Service Acount]. ...n 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
     Started:  1:15:01 AM  Error: 2012-10-17 01:15:03.98
     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: 2012-10-17 01:15:03.99
     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: 2012-10-17 01:15:04.01
     Code: 0xC0016016     
Source:       
Description: Failed to ...  The package execution fa...  The step failed.

这似乎是一个常见问题，但是，我发现的建议都不适用于我的情况，我的实例似乎也与大多数其他情况不匹配.这是有关我的实施的重要细节.

This appears to be a common problem, however, none of the recommendations that I've found either apply to my scenario nor does my instance seem to match most of the other cases where this occurs. Here are the important details regarding my implementation.

• 此软件包正在将数据从iSeries系统导出到SQL Server 2005年数据表.
• 此过程成功运行，但一直崩溃 一方面，特定表导出.实际上，它运行起来没有任何麻烦 持续2个小时以上才能死亡.检查所有关联的属性后 通过这一步，我可以看到这一步没有什么不同 与其他表导出步骤(表/列导出除外)相比 映射.
• 软件包ProtectionLevel设置为DontSaveSensitive，并且iSeries 凭据存储在SQL Server访问的配置文件中.
• 我可以在BIDS的计算机上执行失败的步骤.无论如何，它不会 在服务器上工作，尽管服务器使用的是完全相同的凭据.
• 正如我提到的，我有两个软件包.它们实际上是同一件事，除了 一种是从一个iSeries数据库中导出数据，另一种是从数据库中导出 数据几乎与另一个iSeries数据库的结构完全相同.首先 即使使用相同的iSeries凭据，该程序包也没有任何问题.
• 需要明确的是，我的服务器在几个月内都没有发生任何变化(我知道). 昨天早上才刚开始发生.

• This package is exporting data from an iSeries system, to SQL Server 2005 data tables.
• This process works successfully but keeps crashing on one, specific table export. In fact, it runs without any trouble for over 2 hours before it dies. After inspecting all properties associated with this step, I can see that there is nothing different about this step compared to the other table export steps, other than the table/column export mappings.
• The package ProtectionLevel is set to DontSaveSensitive and the iSeries credentials are stored in a config file that is accessed by SQL Server.
• I can execute the failing step on my machine, in BIDS. Regardless, it doesn't work on the server, though the server is using the exact same credentials.
• As I mentioned, I have two packages. They are effectively the same thing, except one is exporting the data from one iSeries database, and the other is exporting data that is almost the exact same structure from another iSeries DB. The first package doesn't have any trouble even though it is using the same iSeries credentials.
• To be clear, nothing on my server has changed in months (that I am aware of.) This just started happening yesterday morning.

任何提示或想法将大有帮助.导出非常重要，许多用户/工人在日常工作中都依赖此数据.

Any tips, or thoughts would be tremendously helpful. This export is extremely important and many users/worker rely on this data for their day-to-day work.

推荐答案

好吧，我讨厌发布这样的答复，但我已经解决了问题.

Well, I hate having to post such a response but I've solved the problem.

出现此问题的简短答案是因为数据表中的字段之一定义不正确.在这种情况下，它被声明为decimal (11, 3)，并且应该是decimal (13, 3).在将值发布到不符合(11, 3)范围的表中之前，我没有遇到过这个问题.

The short answer reason why I had this problem is because one of the fields in a data table was improperly defined. In this case it was declared as a decimal (11, 3) and it should have been a decimal (13, 3). I didn't experience this problem until a value was being posted to the table that didn't fit the (11, 3) range.

这个问题突显了我对SSIS的最大抱怨之一.有时我会得到错误，这些错误通常在Internet上得到了很好的记录.我搜索所有日志，并在错误消息是真实的假设下尝试设置各种测试方案.但是，当我最终解决问题时，它与写入日志文件的错误消息完全无关.

This issue highlights one of my biggest complaints with SSIS. On occasion I get errors that are often well documented on the internet. I search through all of my logs and I try to set up various test scenarios under the assumption that the error message is honest. Yet, when I finally solve the problem, it's completely unrelated to the error message that is written to the log file.

在这种情况下，上面提到的错误与该问题完全无关吗?！实际上，我很幸运看到了这个问题.我知道我桌子上的更新可能是一个潜在的解决方法，因为我以前见过SSIS这样的误传.

In this case, the error mentioned above had absolutely nothing to do with the problem?! In fact, I was very lucky to see the problem at all. I knew the update on my table might be a potential fix because I've seen SSIS mis-communicate like this before.

我想将这归因于中空客轰炸我的服务器，但是这种经验最好的收获是尝试根据其他人的建议来解决您的SSIS问题，但是 ，如果他们的建议无济于事，则意识到该问题可能与SSIS错误消息无关，请对与故障点相关的所有内容进行三重检查.

I'd like to blame this on neutrinos from space bombarding my server but the best take-away from this experience is to try and solve your SSIS problems based off of the advice of others, however, if their advice doesn't help, realize the issue may be unrelated to the SSIS error message and triple-check everything associated with the point of failure.

这篇关于SSIS:刚开始获得“在指定状态下无效的密钥".我安排的SSIS程序包上的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！