如何从数据库中检索 bigint 并将其放入 SSIS 中的 Int64 中
几年前我遇到了这个问题，希望已经有解决方案，但我只是不知道。我在 SSIS 包的控制流中使用“执行 SQL 任务”来检索“bigint”ID 值。该任务应该将其放置在 Int64 SSIS 变量中，但我收到错误：“分配给变量“User::AuditID”的值的类型与当前变量类型不同。变量在执行期间可能不会更改类型。变量类型是严格的，除了 Object 类型的变量。”
几年前，当我将此问题引起 MS 的注意时，他们表示我必须通过将 bigint 放入 SSIS 对象变量中，然后根据需要将值转换为 Int64 来“解决”这个问题。有谁知道这个问题是否已得到解决，还是我仍然需要“解决”这个混乱的问题？
编辑：
服务器统计
- 产品： Microsoft SQL Server Enterprise Edition
- 操作系统： Microsoft Windows NT 5.2 (3790)
- 平台： > NT INTEL X86
- 版本： 9.00.1399.06
I ran into this problem a couple years back and am hoping there has been a fix and I just don't know about it. I am using an 'Execute SQL Task' in the Control Flow of an SSIS package to retrieve a 'bigint' ID value. The task is supposed to place this in an Int64 SSIS variable but I getting the error: "The type of the value being assigned to variable "User::AuditID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object."
When I brought this to MS' attention a couple years back they stated that I had to 'work around' this by placing the bigint into an SSIS object variable and then converting the value to Int64 as needed. Does anyone know if this has been fixed or do I still have to 'work around' this mess?
edit:
Server Stats
- Product: Microsoft SQL Server Enterprise Edition
- Operating System: Microsoft Windows NT 5.2 (3790)
- Platform: NT INTEL X86
- Version: 9.00.1399.06
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要将 BIGINT 值读入字符串变量 - 而不是对象变量： 为什么我无法存储我的 BIGINT 结果在 Int64 变量中？
You need to read the BIGINT value into a string variable - not an Object variable: Why can’t I store my BIGINT result in an Int64 variable?
我能够使用 ExecuteSQL 任务将 BIGINT 拉入 Int64，没有出现任何问题。如果您的结果集是单行，请确保在 ExecuteSQL 任务中将其设置为单行。如果您的结果集是多行，那么您需要为结果集使用对象类型变量，然后在循环结构中设置该变量。我不认为这是一个“解决方法”，因为一组 BIGINT 值与 BIGINT 不同。
我使用的是 SQL 2008，但无论源数据如何，SSIS 应该（大部分）处理相同的事情。
I was just able to pull in a BIGINT into an Int64 without a problem using an ExecuteSQL task. If your result set is a single row, make sure that you set it as such in your ExecuteSQL task. If your result set is multiple rows then you need to use an object type variable for the result set and then you would set the variable within your looping structure. I don't think this is a "workaround" because a set of BIGINT values is different than a BIGINT.
I'm on SQL 2008, but SSIS should handle things (mostly) the same regardless of the source data.