How to determine row count in SSIS dataset

Question

I frequently encounter a situation in SSIS packages where I run a SQL Command to return a set of rows from an ADO connection. There are cases where I want to branch based on the number of rows returned. The ado resultset is stored in an SSIS 'object' datatype. Is there a way in SSIS expression or Script component to get that count of rows?

Answer 1

You can create a precedence Constraint after the Execute SQL Task to the object datatype variable, @[User::objectvariable]>0 as expression in precedence constraint. However in Execute SQL Task you will get resultset to the object variable.

Answer 2

Instead of using the Execute Sql task, use a dataflow task like this.

1. Use a source component to retrieve your data
2. Use a rowcount component to store your rowcount into a variable
3. Use a recordset destination component and store that in your original variable (system.object type)

Then return to the control flow and continue as you planned, using the rowcount variable to branch your control flow.