CODEWITHSUNDEEP
ssisado
JSacksteder
JSacksteder

Reputation: 800

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?

Upvotes: 6

Views: 14083

Answers (2)

Nanda
Nanda

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

Upvotes: -1

William Salzman
William Salzman

Reputation: 6446

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.

Upvotes: 13

Related Questions