How to Access Parameters of User Variables in SSIS Script Task

Question

I have a variable [User::WorkOrderProductIdList] in SSIS package containing records of a class object.

Work Order Product class

public class WorkOrderProduct
{
    public Guid workOrderId;
    public Guid workOrderProductId;
    public static Guid WorkOrderId { get; set; }
    public static Guid WorkOrderProductId { get; set; }
}

Main Script Task

public override void InputWOProduct_ProcessInputRow(InputWOProductBuffer Row)
{
    ArrayList wopList = new ArrayList();
    WorkOrderProduct wop = new WorkOrderProduct();
    wop.workOrderId = pWorkOrderID;
    wop.workOrderProductId = pWorkOrderProductID;
    wopList.Add(wop);
}

Assign wopList to [User::WorkOrderProductIdList]

public override void PostExecute()
{
    base.PostExecute();
    Variables.WorkOrderProductIdList = this.wopList;
}

In another script task, it takes in [User::WorkOrderProductIdList] as ReadOnlyVariables.

May I know how can I loop through [User::WorkOrderProductIdList] and extract the values of workOrderId and workOrderProductId for each row?

---

I saw that my ArrayList [User::WorkOrderProductIdList] contains the records and values, but there are no functions when . on the field.

Answer 1

Population

Intellisense issue aside, you'll only ever have at most 1 row in there

You are using a Data Flow and within that, you have a Script Component acting as a Transformation.

In InputWOProduct_ProcessInputRow which fires for each row that passes through the component, you have

Every time a new row comes in, you are going to empty out the existing ArrayList and reinitialize it.

Instead, you need to have that variable at the class scope and have the initialization logic in the not-shown PreExecute method

ArrayList wopList;
// Or, if you wish to use the Generics
// List<WorkOrderProduct> wopList 

public override void PreExecute()
{
    base.PreExecute();
    /*
     * Add your code here
     */
    this.wopList = new ArrayList();
    // Or
    // this.wopList = new List<WorkOrderProduct>();

}

Consumption

You use the ArrayList to hold the elements of your array but that is a weakly typed list.

We don't recommend that you use the ArrayList class for new development. Instead, we recommend that you use the generic List class.

When you're enumerating through it in your foreach loop, what is getting popped off the list is of type Object. Not only do I just "know" that, itellisense is telling you that all it knows is the type is Object because it's giving you the functions that everything has because they're all derived from Object.

Yes, the Watch window has inspection magic to show you what the values are but do you think the team that wrote the former is the same team that wrote the latter?

Since you "know" what the type should be, declare it as such.

foreach (WorkOrderProduct wopObj in ...

However, the next logical error, probably, is going to be in the accessing of Variables.WorkOrderProductIdList itself. Your snipped image there shows you're shredding out the array in the PreExecute method. The sequence of operations is that the Data Flow is going to go through validation, then pre-execute sequences so at that point, it's going to shred the results of your array list and the value of wopObj_workOrderId is going to be the last element of your array.