How to determine the number of rows in an SSIS dataset

Question

I often come across a situation in SSIS packages where I run an SQL command to return a rowset from an ADO connection. There are times when I want to fork based on the number of rows returned. The ado result set is stored in the "SSIS" object data type. Is there a way in an SSIS or Script expression to get this number of lines?

Answer 1

Instead of using the Execute Sql task, use a data flow task like this one.

• Use the source component to retrieve your data.
• Use the rowcount component to store your string in a variable
• Use the destination component of the recordset and save it in the source variable (type system.object)

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

Answer 2

You can create a priority constraint after the Execute SQL Task for a variable of the object type of the object @ [User :: objectvariable]> 0 as an expression in the use case. However, in the Execute SQL Task you will get a result set for an object variable.