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

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.

jay-areAsked:
Who is Participating?
 
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
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
 
8080_DiverCommented:
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
 
jay-areAuthor Commented:
How do I query server B's table first?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jay-areAuthor Commented:
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
 
jay-areAuthor Commented:
Sorry, this is the full statement:


declare @myvar as char
if exists(select * from mytable)
set @myvar=0
0
 
8080_DiverCommented:
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
 
jay-areAuthor Commented:
"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
 
Alpesh PatelAssistant ConsultantCommented:
It's good to use Transfer SQL object task. it is developed particularly to transfer SQL objects like table sp, trigger etc. etc.
0
 
SThayaConnect With a Mentor Technical MAnagerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
jay-areAuthor Commented:
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
 
jay-areAuthor Commented:

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
 
jay-areAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Use "AND"   not "OR" , if that didn't help, give me a screenshot :)
v.png
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
green means success, blue means complete.
sop you need it to evaluate success AND the constraint expression
0
 
jay-areAuthor Commented:
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
You shouldn't connect all of them together.
it's separate routes...

see attached for your 2 options
v2.png
0
 
jay-areAuthor Commented:
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
 
jay-areAuthor Commented:
Ah nevermind...had to change those precedences to OR instead of AND.
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Yep like in option 2, it'll be as a dotted line.
where are you stuck at now?
0
 
jay-areAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
jay-areAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Use Int64, maybe it's returning a big value, how many rows it's returning when you try to run it in ssms?
0
 
jay-areAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
jay-areAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
jay-areAuthor Commented:
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
 
jay-areAuthor Commented:
Split the points up, hopefully that's ok.  SThaya's comment was correct, but huslayer was able to help me get it working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.