如何将 sql 查询结果存储在变量和 messegeBox 中 [英] how to store sql query result in a variable and messegeBox

问题描述

我在 ssis 包中的 Execute sql 任务中有一个简单的 sql 查询，SELECT MAX(binindex)从 dbo.myTable

I have a simple sql query in my Execute sql task in ssis package, SELECT MAX(binindex) FROM dbo.myTable

我需要将此最大索引存储到一个变量中，然后将其传递给 Script Task 并显示它，我已经声明了一个包变量，包编译，但是每次都显示-1，我不知道我做错了什么，任何帮助将不胜感激！

I need to store this maximum index into a variable and then pass it to Script Task and display it, I already declared a package variable, the package compiles, however it shows -1 every time, I don't know what I'm doing wrong, any help will be appreciated!

public void Main(){
    //TODO: Add your code here
    Dts.TaskResult = (int)ScriptResults.Success;
    MessageBox.Show(Dts.Variables["User::BININDEX"].Value.ToString());
}

推荐答案

好消息是，就我所见，您所做的一切都是正确的.我重新创建了您的包，并从我的查询中获得了预期值.

The good news, is that you are doing everything correctly as far as I can see. I recreated your package and I get the expected value from my query.

我也可以推断出您的情况 - 我的查询返回了正确的值，但我的包产生了不正确的结果".

I can also induce your situation - the correct value is returned from my query but my package produces an "incorrect result."

我希望问题在于您在不同的范围内定义了两个 BININDEX 变量.我最初的假设是包范围的一个包含值 -1 并且您有一个变量范围为同名的执行 SQL 任务".默认行为是创建范围为当前具有焦点的对象的变量.顺便说一下，这在 2012 版 SQL Server 中发生了变化.

The problem, I hope, is that you have two BININDEX variables defined at different scopes. My original assumption was the Package scoped one contained a value of -1 and you had a variable scoped to the "Execute SQL Task" with the same name. The default behaviour is a variable is created scoped to the object that currently has focus. This changes in the 2012 release of SQL Server by the way.

由于您的图片显示包作用域变量的设计时值为 123，因此也可能存在您在脚本任务中定义的同名 BININDEX 变量.局部变量将覆盖全局范围的变量

As your picture shows a design-time value of 123 for the package scoped variable, the possibility also exists that you have a variable defined on the Script Task with the same name of BININDEX. The local variable would override the globally scoped variable

点击你的脚本任务，希望你会看到一个定义在那里的 BININDEX，就像上面一样.否则，我认为问题出在您的包中的某个地方，您的 BININDEX 变量存在冲突.您可以尝试通过包资源管理器寻找一个实例，其中列出了两个具有相同名称的变量.

Click on your Script Task and hopefully you'll see a BININDEX defined there like the above. Otherwise, I think the problem is somewhere in your package, you have conflicting BININDEX variables. You can try slogging through the Package Explorer looking for an instance where you have two variables with the same name listed.

我需要离开，但如果不是这种情况，请在执行 SQL 任务上添加一个 PostExecute 断点并查看您的本地窗口(不是变量，因为它仅反映设计时值).展开变量，您应该能够看到 BININDEX 的值.那里正确吗?

I need to leave but if none of that is the case, add a PostExecute breakpoint on the Execute SQL Task and look at your Locals window (not Variables as that only reflects Design-time values). Expand Variables and you should be able to see the value of BININDEX. Is it correct there?

这篇关于如何将 sql 查询结果存储在变量和 messegeBox 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！