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.