I have an SSIS package that executes simple queries against a list of servers. I am having an issue with one particular simple query: If a table exists, get the rowcount. Otherwise report "not exists".

The SQL code works in Management Studio if I connect directly, but when I connect with SSIS using an ADO NET data flow task to execute the code I get a validation error. It seems to me that SSIS is examining all objects in the code to see if they exist before executing. It fails before it even tries to run.

If I comment out the "select count(*) from Table1" then everything works no problem.

The distributed servers are all using SQL Server 2008R2 Express Edition.

declare @Result varchar(1000),
        @Table1Count int

select  @Result = '',
        @Table1Count = 0

-- If tables exist, get the counts
IF EXISTS(select 1 from information_schema.Tables where Table_Name = 'Table1')
  select @Table1Count = count(*) from Table1
  select @Result = 'Table1: ' + convert(varchar(12),@Table1Count) 
  select @Result = 'Tables do not exist'

Any ideas?

2 Answers 2


You could try executing the offending statement using sp_executesql.

  • I thought of that, but then the problem of how to pass the value of the count back to a variable, which in turn is being passed back into SSIS so I can further manipulate it.
    – datagod
    Apr 4, 2012 at 0:56
  • 2
    You can use variables with sp_executesql. Here's an example.
    – gonsalu
    Apr 4, 2012 at 9:30
  • Cool. Learn something new every day.
    – datagod
    Apr 4, 2012 at 13:54

One idea - instead of using a script to get the row count, why not simply create a SSIS variable and use the Row Count task within the Data Flow component? All you would need to do is map the Row Count task to the variable you created. Then you can do whatever you want with the variable's value. You may need to use the Conditional Split or Lookup components in the Data Flow to check for the existence of the table before the Row Count is hit, but I would try it either way.

  • Using the Row Count task would bring all the rows into the pipeline, which would be pretty slow (comparing to a COUNT(*)).
    – gonsalu
    Apr 3, 2012 at 20:10
  • I'm afraid I'd have to disagree. I have seen this configuration work in record time when compared to a COUNT( * ) - could have to do with the index bitmap lookup that accompanies the ( * ). Apr 3, 2012 at 22:40
  • 3
    It's impossible for the Row Count task to be faster than a simple COUNT(*) at the source.
    – gonsalu
    Apr 4, 2012 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.