如何将列值存储到数据流任务中的变量中? [英] How to store a column value into a variable in Data Flow Task?

问题描述

我有 DFT，我从表中提取了一个 URL，我需要将该值存储到一个变量中.

I have DFT, where I extracting a URL from a table and I need to store that value into a variable.

我正在使用 OLEDB 源连接到我的 SQL Server 并选择所需的列.然后我有条件拆分，它将为我拆分正确的 URL.来自我已映射到脚本组件的条件拆分的正确 URL 流.

I am using OLEDB Source to connect to My SQL Server and selecting the required columns. Then I have Conditional Split which will split the right URL for me. The right URL flow from a conditional split I have mapped to Script Component.

以下是我使用过的 C# 代码:

Below is the C# code I have used:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    this.ReadWriteVariables["User::NextPageLink"].Value = Row.href;
}

但是当我尝试执行时，出现以下错误:

But when I try to execute I am getting a below error:

[脚本组件 [116]] 错误:Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException:为读写访问锁定的变量集合在 PostExecute 之外不可用.在 Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)在 Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)在 Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

[Script Component [116]] Error: Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

推荐答案

在脚本组件中，变量只能在 PostExecute 方法中写入，而不是 ProcessInputRow> 方法.要使用列中的值更新变量，请在这些方法之外声明一个变量，更新 ProcessInputRow 中的变量，然后将此值分配给 PostExecute 中的 SSIS 变量方法.当然，还要在脚本组件的 ReadWriteVariables 字段中添加 SSIS 变量.您可能知道，ProcessInputRow 方法为每一行调用一次，而 PostExecute 仅在处理完所有记录后才执行，因此如果您需要执行某些操作您需要在 ProcessInputRow 方法中执行此逻辑的多行值.请注意为通过该组件的每条记录执行任何操作所带来的额外开销.

In a Script Component variables can only be written to in the PostExecute method, as opposed to the ProcessInputRow method. To update a variable with the value from a column, declare a variable outside of these methods, update the variable in ProcessInputRow, and assign this value to an SSIS variable in the PostExecute method. And of course add the SSIS variable in the ReadWriteVariables field of the Script Component. As you may be aware, the ProcessInputRow method is called once for each row while PostExecute is only executed after all the records have been processed, so if you need to do something with values from multiple rows you'll want to perform this logic within the ProcessInputRow method. Just be aware of the additional overhead that comes with performing whatever actions you're doing for each record that comes through this component.

string nextPageLink = null;

public override void PostExecute()
{
    base.PostExecute();
    Variables.NextPageLink = nextPageLink;  
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    nextPageLink = Row.href;
}

这篇关于如何将列值存储到数据流任务中的变量中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！