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!

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

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

Answer 3

The way I went about it, keep in mind I already had a ForEachLoop container that looped through excel files.

1. Created Count Variable with DataType=INT Value=1
2. Dragged Expression Task below my Data Flow and attached precedence
3. Inserted this function in the Expression Task

@[User::Count] = @[User::Count] + 1

Each time the file loops the Count variable will increase by 1. Hope this Helps anyone searching this question.