CODEWITHSUNDEEP
c#sql-serverssisetlscript-component
gtrivedi
gtrivedi

Reputation: 515

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

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

enter image description here

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.

Upvotes: 3

Views: 4055

Answers (2)

Ferdipux
Ferdipux

Reputation: 5256

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.

Upvotes: 1

Hadi
Hadi

Reputation: 37368

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:

Upvotes: 4

Related Questions