How to determine row count in SSIS dataset

问题:

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?

回答1:

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.

回答2:

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.