SSIS Count of Object Variable?

Question

I am using SSIS in BIDS 2008 and I am trying to get a count of an Object Variable containing a list of files. Any idea on how to do this?

Thanks!

Solution 1

How are you populating this Object Variable? Depending on how you populate the variable, more eligant ways of getting the count can be listed. However, here is a quick and dirty solution for you:

1. Create a new variable of type integer and initialize the variable to 0
2. Use a foreach loop task to iterate over your object (open the foreach loop editor, go to Collections and change the Enumerator to Foreach ADO Enumerator and the ADO object source variable to your Object variable.
3. Create a Execute SQL Task
4. On the general tab, set ResultSet to Single row and your sql statement to SELECT (@Count + 1)
5. In the Parameter Mapping tab, add your new integer variable and change the Parameter Name attribute to @Count
6. In the Result Set tab, add a new entry with name: 0 and variable name: your newly created integer variable.

Once your looping completes, you will have the count of the object.

NOTE: If you are already iterating through these files (which I assume you are since you are using SSIS to populate an Object Variable with file names), you need only perform steps 1, 3-6.

Solution 2

Is there a reason you don't just use a script task?

Given two SSIS variables, MyObject (type Object) and MyObjectCount (Int32) set to 10.

Script task 1 fills the object.

    // Nothing clever, just fills a List<T> with 
    // MyObjectCount's worth of values.
    public void Main()
    {
        int count = (Int32)Dts.Variables["User::MyObjectCount"].Value;
        List<bool> obj = new List<bool>();
        for (int i = 0; i < count; i++)
        {
            obj.Add(false);
        }

        Dts.Variables["User::MyObject"].Value = obj;
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Script task 2 simply assigns the value of the SSIS variable MyObject into a local variable which I cast into the appropriate type. I compare that count to my reference count and they are the same.

    public void Main()
    {
        int refCount = (Int32)Dts.Variables["User::MyObjectCount"].Value;
        List<bool> obj = Dts.Variables["User::MyObject"].Value as List<bool>;
        int actualCount = obj.Count;
        string message = string.Format("Reference count: {0}. Actual count {1}", refCount, actualCount);

        bool fireAgain = false;
        Dts.Events.FireInformation(0, "Accessing count", message, string.Empty, 0, ref fireAgain);
        Dts.TaskResult = (int)ScriptResults.Success; Dts.TaskResult = (int)ScriptResults.Success;
    }

Results

[Accessing count] Information: Reference count: 10. Actual count 10

If you aren't responsible for what's pushing the object into the SSIS variable, then you might need to wrap the above extractions into a series of try/catch blocks or cast it as a type until you find something that the run-time can convert into a strongly typed object.

Comments

[buzzzzjay]

Sounds great, that's what I was afraid of. I wish during debugging you could see the values an object contains and just easily do an Object.Count. Thanks!