How to store a column value into a variable in Data Flow Task?

Question

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

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.

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:

[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)

It seems like there is an issue with the ReadWriteVariable permission. Did you add your variable to "ReadWriteVariables" field in Script Transformation Editor? — Sabbir Hassan, Jan 22 '19 at 13:46

score 3 · Accepted Answer · answered Jan 22 '19 at 14:51

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;
}

Thanks for your answer. – Naveen Kumar Jan 22 '19 at 17:16 — Naveen Kumar, Jan 22 '19 at 17:16

score 1 · Answer 2 · answered Jan 22 '19 at 17:23

Thanks, @userfl89. I have coded similarly to yours but I have used VariableDispenser. Does it really need here?

public override void PostExecute()
{
    base.PostExecute();
    VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
    variableDispenser.LockForWrite("User::NextPageLink");
    this.ReadWriteVariables["User::NextPageLink"].Value = nextpagelink;

 }

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

How to store a column value into a variable in Data Flow Task?

2 Answers2

Linked