How to check for number of rows in recordset using constraint?

Question

How to check for number of rows in recordset using constraint?

blasto_max

SSCertifiable

Points: 5025
More actions
January 3, 2014 at 12:13 pm

#286695

I have an execute SQL task which is connected to a script. The SQL initializes variables to be used by the script. Insided the script, I can check if the SQL recordset has > 0 rows and then do its job. But, is there a way to do that checking inside the connector which connects SQL task to Scrip task, ie in the form of a constraint ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

Yes, you can do that. What is your Execute SQL task doing? If you are returning a count of the rows in the ResultSet as part of the SQL task, you can assign that count to a variable and set an 'Expression and Constraint' on the connector between the Execute SQL task and the Script task. The constraint would be set to 'Success' and the expression would be set to @YourVariable > 0.

If you set the variable in your Execute SQL task, you could add an extra Execute SQL task before the Script task and do it there.

Regards

Liam

blasto_max SSCertifiable Points: 5025 More actions · Answer 2

Lempster (1/6/2014)
Yes, you can do that. What is your Execute SQL task doing? If you are returning a count of the rows in the ResultSet as part of the SQL task, you can assign that count to a variable and set an 'Expression and Constraint' on the connector between the Execute SQL task and the Script task. The constraint would be set to 'Success' and the expression would be set to @YourVariable > 0.
If you set the variable in your Execute SQL task, you could add an extra Execute SQL task before the Script task and do it there.
Regards
Liam

I am not returning any count. Looks like getting and setting it inside a script task is the option to go with.

Lempster SSCoach Points: 15651 More actions · Answer 3

You may not be returning a row count, but you must be returning some rows using a SELECT statement, right, otherwise your Resultset will always be empty? All you need to do is add an extra column to the SELECT query to get the row count and then assign it to a variable.

For example, if you currently have something akin to:

SELECT col1 as varA, col2 as varB FROM yourtable

then modify it to:

SELECT col1 as varA, col2 as varB, COUNT(*) as NumRows FROMyourtable

Am I misunderstanding your requirement?