SSIS:刚开始收到“密钥在指定状态下无效".我预定的 SSIS 包出错

2021-12-30 00:00:00 sql-server-2005 ibm-midrange sql-server ssis bids

我的 SQL Server 2005 机器上有 2 个计划作业，计划每天早上(大约凌晨 2:00)运行.多年来，这些工作(大部分)都运行良好，虽然我遇到了一些小问题，但我不得不解决这个问题，这完全让我感到困惑.

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 Server2005年数据表.
  • 这个过程成功但一直崩溃一、具体表导出.事实上，它运行没有任何问题超过 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.

相关文章