SSIS read a System.Object variable and write to it in Script Component...How?

Question

I have a SSIS package which starts with a Script Task which initializes an empty DataTable and assigns it to a user variable. I'm adding some sample rows as I'm still doing development. The variable is called: FlatFileBadRowDataTracker in SSIS.

public void Main()
{
    // TODO: Add your code here
    string SSISRunStartTimeStamp = DateTime.Now.ToString("yyyyMMddHHmmss");
    Dts.Variables["User::SSISRunStartTimeStamp"].Value = SSISRunStartTimeStamp;
    Dts.Variables["User::FlatFileBadRowDataTracker"].Value = BuildSampleDataTable();


    Dts.TaskResult = (int)ScriptResults.Success;
}

private DataTable BuildSampleDataTable()
{
    DataTable dt = new DataTable();

    // ErrorColumn
    DataColumn errorColumn = new DataColumn("ErrorColumn");
    errorColumn.DataType = System.Type.GetType("System.String");
    errorColumn.DefaultValue = string.Empty;
    dt.Columns.Add(errorColumn);

    // ErrorDescription
    DataColumn errorDescription = new DataColumn("ErrorDescription");
    errorColumn.DataType = System.Type.GetType("System.String");
    errorColumn.DefaultValue = string.Empty;
    dt.Columns.Add(errorDescription);

    // FileName
    DataColumn fileName = new DataColumn("FileName");
    errorColumn.DataType = System.Type.GetType("System.String");
    errorColumn.DefaultValue = string.Empty;
    dt.Columns.Add(fileName);

    // RawData
    DataColumn rawData = new DataColumn("RawData");
    errorColumn.DataType = System.Type.GetType("System.String");
    errorColumn.DefaultValue = string.Empty;
    dt.Columns.Add(rawData);

    // ErrorDescription
    DataColumn dataFlowComponent = new DataColumn("DataFlowComponent");
    errorColumn.DataType = System.Type.GetType("System.String");
    errorColumn.DefaultValue = string.Empty;
    dt.Columns.Add(dataFlowComponent);

    // Populate with some sample data.
    DataRow row;
    for (int i = 1; i < 5; i++)
    {
        row = dt.NewRow();
        row["ErrorColumn"] = "ErrorColumn" + i;
        row["ErrorDescription"] = "ErrorDescription" + i;
        row["FileName"] = "FileName" + i;
        row["RawData"] = "RawData" + i;
        row["DataFlowComponent"] = "SSIS_DataFlowTask_" + i;
        dt.Rows.Add(row);
    }

    return dt;
}

#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
/// 
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

Then I have a Data Flow Task which reads a text file (csv through a Flat File Source component) and has a Script Component (as transformation) which determines whether the rows are good or bad. Good rows are send to a 'GoodRow' output and bad rows are sent to 'BadRows' output from the Script Component. Usually a file will have mostly good rows but a few will have data errors (example: the email column has something which doesn't resemble an email address), in which case the component has to send this to BadRows output. My goal is to capture the error related data (such as the column name which has the data error and some description along with that) into the same DataTable I created in the Script Task earlier.

I have added this variable in the ReadWriteVariables under the Properties of the Script Component Editor screen. I'm using it in the PreExecute() to obtain the schema of the DataTable and assign it to dt which is a local variable I'm declared at the beginning of the class.

public override void PreExecute()
{
    base.PreExecute();
    dt = (DataTable)Variables.FlatFileBadRowDataTracker;

}

Then I try to add data into dt as and when I find data related errors in Input0_ProcessInputRow(Input0Buffer Row) method.After that in the PostExecute() I try to assign dt back to the user variable.

public override void PostExecute()
{
    base.PostExecute();
    Variables.FlatFileBadRowDataTracker = dt;
}

However, when I run the package I get this error (shown below) which tells me that I cannot use the variable in the PreExecute() method. It seems I can only use it in PostExecute() method. I need the existing data+schema of the datatable, otherwise I will have to recreate the schema and I'll lose the data (For now its just the test data as shown in code).

Is there any way I can get the schema+data of the DataTable in my Script Component? The Script Component also doesn't let me add the variable in both ReadOnlyVariables and ReadWriteVariables. It seems I can add it to only one of them.

score 4 · Accepted Answer · answered Feb 10 '20 at 21:01

4

Try using Variable dispenser instead of selecting variable as ReadWrite Variable:

Pre-Execute phase:

IDTSVariables100 vars = null;
VariableDispenser.LockForRead("User::FlatFileBadRowDataTracker");
VariableDispenser.GetVariables(out vars);
dt = (DataTable)vars["User::FlatFileBadRowDataTracker"].Value;
vars.Unlock();

Post-Execute phase:

IDTSVariables100 vars = null;
VariableDispenser.LockForWrite("User::FlatFileBadRowDataTracker");
VariableDispenser.GetVariables(out vars);
vars["User::FlatFileBadRowDataTracker"].Value = dt;
vars.Unlock();

For Additional information, refer to:

Unable to fetch "ReadWrite" Variable Value in Script Component of SSIS

answered Feb 10 '20 at 21:01

Hadi

36,233
13
65
124

Thanks @Hadi. If I use this same approach in another Script Component within the same Data Flow Task, could this result in any locking conflict? Do I need to Lock() it after I Unlock() ? – gtrivedi Feb 11 '20 at 08:41
I don't think that you can use a variable multiple time in the same data flow task since the value is committed at the end of execution of the data flow (the value will not be affected within the data flow). Regarding the variable lock i think it may cause a conflict, but it is good to try it. @gtrivedi – Hadi Feb 11 '20 at 08:57

Ferdipux · Answer 2 · 2020-02-12T12:32:38.897

1

On the reason why you receive such error messages. Read-Write Variables are available only at PostExecute method; Microsoft did this to reduce chances of congestion. So, your error message is thrown at PreExecute method.
Hadi's recommendations should do the trick of accessing your RW variable before running Script Component PostExecute method.

edited Feb 12 '20 at 12:32

answered Feb 11 '20 at 08:43

Ferdipux

5,116
1
19
33

SSIS read a System.Object variable and write to it in Script Component...How?

2 Answers2