CODEWITHSUNDEEP
sql-serverssis
Al__H
Al__H

Reputation: 306

SSIS step needed to count number of rows and error if zero

I need to check if a table has been populated before proceeding in an SSIS package (SQL2008 R2).

My thoughts were to add an Execute SQL task, and use a CASE statement, but it's not the correct format.

CASE
WHEN (SELECT COUNT(*) FROM STAGING.dbo.tableX) <1
    THEN RaisError('NO ROWS',18,1)
ELSE 
END AS X

This should be easy, but I am stymied. Thanks

Upvotes: 2

Views: 3488

Answers (1)

rvphx
rvphx

Reputation: 2402

1) Create a variable of type INT

2) On the Execute SQL Task, set the result set to "Single Row".

3) Go To Result Set tab on Execute SQL Task and refer to the variable you created on step 1

4) Set the result Name as "0".

5) Configure the precedence constraint as @VariableName > 1 (This will let the package go to the next step when the row count is > 1

You can change the condition on step 5 to do what you want with the package. This was just an example.

Upvotes: 1

Related Questions