将查询结果传递给SSIS中的变量(Passing query results to variable in SSIS)

我有一个运行执行SQL任务的SSIS包来获取行计数，将其存储在变量中，然后使用脚本任务读取该变量并检查它是否大于0。

我遇到的问题是，当我调试包时，它将变量读取为-1（我假设只是SQL表示查询已成功执行），而不是数据库中的38,000行。据我所知，一切都正确设置并以正确的方式编码，所以我不确定是什么原因引起的。

执行SQL任务设置：

脚本任务代码：

private byte[] emptyBytes = new byte[0]; public void Main() { int rowCount = Convert.ToInt32(Dts.Variables["User::SMART_rowcount"].Value.ToString()); if (rowCount > 0) { Dts.TaskResult = (int)ScriptResults.Success; } else { Dts.Log("The SMART Row Count Check has failed due to result set having no rows. Check table SMART.[SMART_SEC_MGMT_QUOTE_DATA].", (int)Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure, emptyBytes); Dts.TaskResult = (int)ScriptResults.Failure; } }

编辑：

以下是这些和变量的流程：

I have a SSIS package that runs a Execute SQL Task to get a row count, stores that in a variable, and then using a Script Task reads that variable and checks to see if it's greater than 0.

The issue I am having is that when I debug the package, it's reading the variable as -1 (which I assume is just SQL saying the query executed successfully), instead of the 38,000-some rows that are in the DB. As far as I can tell, everything is set up correctly and coded the right way, so I'm not sure what could cause this.

Execute SQL Task settings:

Code from Script Task:

private byte[] emptyBytes = new byte[0]; public void Main() { int rowCount = Convert.ToInt32(Dts.Variables["User::SMART_rowcount"].Value.ToString()); if (rowCount > 0) { Dts.TaskResult = (int)ScriptResults.Success; } else { Dts.Log("The SMART Row Count Check has failed due to result set having no rows. Check table SMART.[SMART_SEC_MGMT_QUOTE_DATA].", (int)Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure, emptyBytes); Dts.TaskResult = (int)ScriptResults.Failure; } }

EDIT:

Here is the flow of these and the variables:

最满意答案

在“执行SQL任务”的“结果集”选项卡中，不要将列的名称放在“结果名称”列中。您放置列的从零开始的索引。

因此，不要在Result Name下使用“Smart_Count”，而应该为“0”。

So I had everything set up correctly and there were no issues with my process. To resolve this error, I deleted the Execute SQL Task, remade it with all of the exact same settings and everything, and it is now returning the correct result.

"Have you tried turning it off and back on again?"

更多推荐