从 SSIS 脚本任务/脚本组件返回输出参数或变量 [英] Returning Output Paramters or Variables From SSIS Script Task / Script Component

问题描述

是否可以从使用 xp_cmdshell 的存储过程调用的 SSIS脚本任务"返回输出参数?

Is it possible to return output parameters from a SSIS "Script Task" that has been called by a stored procedure using xp_cmdshell?

到目前为止我发现的所有示例都展示了如何为 dts 包变量等赋值；并通过消息框显示它们，但我看到的每个示例都显示脚本任务仅返回 Dts.TaskResult = (int)ScriptResults.Success;或 Dts.TaskResult = (int)ScriptResults.Failure ...

All the samples I have found so far show how to assign values to dts package variables etc; and show them via a message box but every single sample I see shows the script task only returning a Dts.TaskResult = (int)ScriptResults.Success; or Dts.TaskResult = (int)ScriptResults.Failure ...

基本上，我有一个脚本任务，它从按预期工作的 dll 调用中获取多个值；值通过消息框进行验证；但我一直无法弄清楚如何将这些返回到最初执行 SSIS 包的存储过程.

Basically, I have a script task that gets several values from a dll call that works as anticipated; values are verified with message boxes; but I have not been able to ferret out how to return those back to the stored procedure that originally executed the SSIS package.

我是否遗漏了一些明显的东西?请提供一个功能代码示例 &或控制流/数据流等的屏幕截图，了解如何端到端地执行此操作... 例如:从存储过程 -> 执行 *.dtsx 包，向其传递参数，包括 OUTPUT 参数；以及相同的存储过程如何在这种类型的调用中读取这些输出参数；返回结果时...

Am I missing something obvious? please provide a functional code example & or screen shots of control flow / data flow, etc., of how to do this end to end... E.G.: From a stored procedure -> Execute a *.dtsx package, passing it parameters, including OUTPUT parameters; and how that same stored procedure can read those output parameters in this type of call; when the results are returned...

提前致谢.

推荐答案

我不知道是否可以将脚本任务中的值返回到您的调用存储过程，但这是我将执行此操作的另一种方法，并且可能得到你的最终结果.创建可以捕获脚本任务中当前显示在消息框中的值的本地 SSIS 变量.例如创建一个名为 mySSISVariable:

I don't know if it is possible to return values in the script task to your calling stored procedure, but here's an alternative way I would do this and may get you your end result. Create local SSIS variables that can capture the values in your script task that are currently being shown in your message boxes. For example create one called mySSISVariable:

在您的任务的脚本屏幕上，确保选择 mySSISVariable 作为 ReadWriteVariables:

On the Script screen of your task make sure to select mySSISVariable as ReadWriteVariables:

现在在您的脚本任务中，将此 SSIS 变量分配给您当前输出到消息框的任何内容:Dts.Variables["User::mySSISVariable"].Value = ReturnValue; 现在 SSIS 有了这个存储在变量中的值，您可以在调用新存储过程时使用它，在执行 SQL 任务中，您的调用存储过程应该对这些变量执行任何操作(对于多个参数不断添加?代码>usp_MyNewStoredProcedure ?, ?, ?:

Now in your script task assign this SSIS variable to whatever you are currently outputting to message boxes:Dts.Variables["User::mySSISVariable"].Value = returnedValue; Now that SSIS has this value stored in a variable you can use it when calling your new stored procedure, that would have done whatever your calling stored procedure was supposed to do with these variables, in an Execute SQL Task(for more than one parameter keep adding ? as such usp_MyNewStoredProcedure ?, ?, ?:

确保将您的 SSIS 用户变量分配为输入参数(在本例中我使用了 OLE DB，这 link 展示了如何使用其他人来实现这一点):

Making sure to assign your SSIS user variable as an input parameter(in this example I used OLE DB, this link shows how this can be accomplished using others):

这篇关于从 SSIS 脚本任务/脚本组件返回输出参数或变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！