Menu
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?
845
Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).
The Row Count transformation counts rows as they pass through a data flow and stores the final count in a variable. A SQL Server Integration Services package can use row counts to update the variables used in scripts, expressions, and property expressions.
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.
Instead of using the Execute Sql task, use a dataflow task like this.
Then return to the control flow and continue as you planned, using the rowcount variable to branch your control flow.
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
