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.