How to determine row count in SSIS dataset

ado, ssis

Solution

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

- Use a source component to retrieve your data

- Use a rowcount component to store your rowcount into a variable

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

Problem

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?

Original source