SSIS Data flow task runs by itself but not as part of package

Question

I have a dtsx package which contains a bunch of data flows.

All of them select rows from table, each of the rows contains a column with some JSON, there is a script component (C#) which deserializes the JSON, then a data destination which loads the data up to some buffer tables.

One of the most simple scripts which deals with some really simple JSON runs perfectly by itself (Right click -> Execute Task, or when all other tasks are disabled), however when I try to run the package as a whole this package falls over with the error message

Index was outside the bounds of the array. at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

The JSON looks like this...

{
"token": "0b683877-81d6-4dhs-a1ad-9fcfff6acb61",
"email": "[email protected]"
}

and the c# like this

public class RootAttributes
{
    public string token { get; set; }
    public string email { get; set; }
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    String myEvtData = System.Text.Encoding.Unicode.GetString(Row.evtdata.GetBlobData(0, Convert.ToInt32(Row.evtdata.Length)));
    JavaScriptSerializer js = new JavaScriptSerializer();
    RootAttributes jRow = js.Deserialize<RootAttributes>(myEvtData);

    //DB Output
    Output0Buffer.AddRow();
    Output0Buffer.country = Row.country;
    Output0Buffer.ord = Row.ord;
    Output0Buffer.aggregateversion = Row.aggregateversion;
    Output0Buffer.clienttype = Row.clienttype;
    Output0Buffer.aggregateid = Row.aggregateid;
    Output0Buffer.updby = Row.updby;
    Output0Buffer.evttimestamp = Row.evttimestamp;
    //JSON Output
    Output0Buffer.token = jRow.token;
    Output0Buffer.email = jRow.email;
}

I have a second script - virtually identical which also fails with the same error message. I have tried running them both one after the other and independently - i.e. all DFs at once.

Please help

Thanks

Mike

KeithL KeithL · Accepted Answer · 2018-02-15T15:02:20

If you are just adding those two columns, then you don't need:

//DB Output
    Output0Buffer.AddRow();
    Output0Buffer.country = Row.country;
    Output0Buffer.ord = Row.ord;
    Output0Buffer.aggregateversion = Row.aggregateversion;
    Output0Buffer.clienttype = Row.clienttype;
    Output0Buffer.aggregateid = Row.aggregateid;
    Output0Buffer.updby = Row.updby;
    Output0Buffer.evttimestamp = Row.evttimestamp;

Just add the two columns to the Output0Buffer.

You just need to check the one column that has the JSON on the middle page.

SSIS Data flow task runs by itself but not as part of package

2 Answers