Solved

Check to see if rows exist in table before truncate/transfer

Posted on 2011-09-20
30
2,064 Views
Last Modified: 2013-11-10
Hello experts

I use some SSIS packages to transfer tables from one server to another.  These are all smaller tables, so I don't want to use replication for them.  The current flow is:  truncate table on server A, then a data flow task that transfers data from server B to server A.  The issue is that sometimes the tables on server B are empty, so it writes out a blank table to server A.  I want to stop this by using a 'if exists' statement first.  I need to do this before the truncate task runs.  How do I check the table on server B before I truncate the table on server A?

I know SSIS has a built-in row count transformation task, but from what I read the 'if exists' function is much faster since I don't actually need to know the row count, just that the table actually has data in it.

0
Comment
Question by:jay-are
  • 16
  • 10
  • 2
  • +2
30 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 36568426
I would execute a query against Server B's table to get the number of rows (which I would capture in a Package Level Variable) and then have a scrip task that, if the row count is 0, fails.  I would then log to the Application Log and notify via someone (email or whatever) that the row count was zero and end the package.
0
 

Author Comment

by:jay-are
ID: 36568523
How do I query server B's table first?
0
 

Author Comment

by:jay-are
ID: 36568570
I just added an execute task that uses server B's connection.  My sql statement is:  if exists(select * from mytable).  How do I set the result as a variable?
0
 

Author Comment

by:jay-are
ID: 36568588
Sorry, this is the full statement:


declare @myvar as char
if exists(select * from mytable)
set @myvar=0
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 36568598
Your query shouldn't have the "IF" in it.  Just query the table and receive the results in a variable:

SELECT COUNT(*)
FROM TableB

Open in new window


You'll have to have the Execute SQL Task connected to ServerB and the Package Variable set as the variable to receive the Result Set.
0
 

Author Comment

by:jay-are
ID: 36568908
"and the Package Variable set as the variable to receive the Result Set."

This is the part I'm having trouble with.  My experience with SSIS packages is pretty limited.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36571621
It's good to use Transfer SQL object task. it is developed particularly to transfer SQL objects like table sp, trigger etc. etc.
0
 
LVL 11

Assisted Solution

by:SThaya
SThaya earned 100 total points
ID: 36572742
Hi ,

Please try the below steps

1. create a variable name like @count
2. in the fisrt EXecute SQL task  , write a query like select count (*)  as cnt from [tbl] and assign the value to the variable @count
3.select  add a Procedent consraint , in the  editor select Evaluation expression as "Expression " and Expression @count ==0 ...
4. select  add a Procedent consraint , in the  editor select Evaluation expression as "Expression " and Expression @count>=1, then do your truncate operation .
5. Data flow task for Data Transfer Screenshot ]
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36574185
jay-are, are you checking server B if it has data before you truncate A?
and if it has, then truncate, if it's empty don't truncate A ?

Please explain more.
Thanks
0
 

Author Comment

by:jay-are
ID: 36574523
I'm sure I'm making this harder than it needs to be.  I can't get the expression to validate in the precedence editor.  My execute task is this:


declare @myvar as int
if exists(select * from mytable)
set @myvar=0
else
set @myvar=1

I like the EXISTS function because it only checks to see if there is one row in the table.  That's all I need.  I don't need to scan the entire table to get a row count.  I just want to make sure it isn't empty.

When I use: @myvar=0 for the expression I get an error.  
"The expression is required to evaluate to true or false: '@myvar=0'."

0
 

Author Comment

by:jay-are
ID: 36574657

jay-are, are you checking server B if it has data before you truncate A?
and if it has, then truncate, if it's empty don't truncate A ?

Please explain more.
Thanks

Yes huslayer, that's exactly what I want to do.  I just need to alter all my existing SSIS packages to do this.
0
 
LVL 21

Accepted Solution

by:
huslayer earned 400 total points
ID: 36574799
Alright...
1-create a INT type variable.
2-add "execute sql task" returns single row into the previous variable, add that query (select count(*) from table)

3-drop 2 precedence constraints to check the counts, if ==0 means no rows, and if > 0 means there's rows.

see the attached

a
let me know if that answered your question and if you need more help with any step.

Regards,
Jason
0
 

Author Comment

by:jay-are
ID: 36575003
Ok I made much more progress this time.  It executes the first task but doesn't go past it.  

The query for the execute task that checks the table on serverB:  


select count(*) from mytable;
go

Then I created 2 precedence constraints just like you said.  If there are rows it goes to the truncate task, if no rows it skips to the next series of tasks.  For some reason it runs the very first execute (checking table on serverB) and says the entire package is complete without moving to the next tasks.  What did I miss?
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36575086
Use "AND"   not "OR" , if that didn't help, give me a screenshot :)
v.png
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36575090
green means success, blue means complete.
sop you need it to evaluate success AND the constraint expression
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jay-are
ID: 36575222
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36575297
You shouldn't connect all of them together.
it's separate routes...

see attached for your 2 options
v2.png
0
 

Author Comment

by:jay-are
ID: 36575426
My package is one long chain of truncates and data flows.  I just want to skip that first one if the table on server B is empty.  How do I chain these tasks together so once it finishes the check of serverB's table and either truncates or doesn't, it continues to the next series of truncate/data flow?
0
 

Author Comment

by:jay-are
ID: 36575440
Ah nevermind...had to change those precedences to OR instead of AND.
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36575487
Yep like in option 2, it'll be as a dotted line.
where are you stuck at now?
0
 

Author Comment

by:jay-are
ID: 36575521
The precedences for checking serverB's table are

@myvar>0
@myvar==0

It always uses the "==0" precedence even though the table on serverB has rows.  

Is there something wrong with the initial execute task?


select count(*) from mytable;
go


I have the myvar variable set up as Int32, Value=0.  Should it be something other than Value=0?
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36575587
You might need to alias the resultset, see attached...if not, give me a screenshot of your execute sql task configurations
1.png
2.png
0
 

Author Comment

by:jay-are
ID: 36575699
No result rowset is associated with the execution of this query.
Error: 0xC002F309 at check serverB for empty table, Execute SQL Task: An error occurred while assigning a value to variable "accountchart": "Exception from HRESULT: 0xC0015005".

This is after I copied your steps.

ssisexec.png
ssisexec1.png
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36575769
Use Int64, maybe it's returning a big value, how many rows it's returning when you try to run it in ssms?
0
 

Author Comment

by:jay-are
ID: 36575891
It returns 2,247 rows.  

I changed the package variable to Int64.  This is the error when the table check runs:

Error: 0xC002F309 at check serverB for empty table, Execute SQL Task: An error occurred while assigning a value to variable "accountchart": "The type of the value being assigned to variable "User::accountchart" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36576017
that's strange!! it should fit nicely in Int32!!
I even did the exact count and worked fine !!

what SQL version do you have ?!  am not sure why i'm even asking that !! but I'm testing on 2008R2 !!

Also can you add a BREAKPOINT and check the variable's value in real time, as in my screenshot?
aaa.png
0
 

Author Comment

by:jay-are
ID: 36576084
Pretty sure this is server 2008r2.  Sql shows version 10.0.4064.  I think it is all up to date.  Breakpoint shows value for my variable at 2247.  
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36579870
I'm not sure!! are you sure that the variable's data type is Int32?
delete the variable and recreate it again, or check the spelling and the case of the spelling !
0
 

Author Comment

by:jay-are
ID: 36581476
Ok, I deleted the variable and the execute task.  Then created the variable again along with the task.  Named the variable and the resultset all the same name and it works.  It must of been something with the naming of the variable and how I named the results of select count(*) from mytable.  I changed it to select count(*) AS MYVAR from mytable.  That might have been it!  Thanks for your patience!
0
 

Author Closing Comment

by:jay-are
ID: 36581528
Split the points up, hopefully that's ok.  SThaya's comment was correct, but huslayer was able to help me get it working.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now