Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Trudye
Posting Yak Master
169 Posts |
 Posted - 2009-02-10 : 13:57:13
|
| I want to check the record count of a SQL Server table and if there are zero records stop execution of my pkg. and send an email. Does anyone have any ideas?Do I have to place the record count in a variable to use a Precedence constraint? And if so how do I get the record count into a variable? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-02-10 : 14:20:30
|
| when you say "record count of SQL Server table" do you mean number of tables in SQL server or number of rows in a given table? To get total tables (user) you can use sys.objects where type = 'U', and to get record count you can use count(*) into a variable.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Trudye
Posting Yak Master
169 Posts |
Posted - 2009-02-10 : 16:56:38
|
| dinakar thanks so much for responding so quickly. I meant rows in a table. Can you tell me how to get a record count into a variable?Thanks againTrudye |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-10 : 17:36:22
|
| Have a SQL task and have your statement likeSELECT COUNT(*) AS TotCount FROM TABLE.Set this resultset to a variable.Add an expression in the output of this task and check for the @TotCount = 0, and based on that result you can perform your subsequent tasks. |
|
|
|
Trudye
Posting Yak Master
169 Posts |
Posted - 2009-02-11 : 12:57:39
|
| Thanks for responding. This is what I am getting: Execute Task Select Stmnt: SELECT COUNT(trans) AS NoRFIrecs From temp_ImportI received the following error :Error: 0xC002F309 at Ck for RFI recs, Execute SQL Task: An error occurred while assigning a value to variable "NoRFIrecs": "Unable to find column NoRFIs in the result set.".Constraint Expression: @NoRFIrecs <= 0As you can see NoRFIrecs is defined in the Variable area of the SSIS pkg and has the scope of the entire pkg. Can you see what I am doing wrong? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-12 : 10:53:52
|
| Nope. you need to define a variable inside package called NoRFIs. then use it in parameter mapping tab of execute sql task to get count value. then use it in constraint expression as @[User::NoRFIs]<=0 |
|
|
|
|
|
|
Contraints and variables