Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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')
BEGIN
  select @Table1Count = count(*) from Table1
  select @Result = 'Table1: ' + convert(varchar(12),@Table1Count) 
END
ELSE
  select @Result = 'Tables do not exist'

Any ideas?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

You could try executing the offending statement using sp_executesql.

share|improve this answer
    
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 '12 at 0:56
2  
You can use variables with sp_executesql. Here's an example. –  Gonsalu Apr 4 '12 at 9:30
    
Cool. Learn something new every day. –  datagod Apr 4 '12 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.

share|improve this answer
    
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 '12 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 ( * ). –  OliverAsmus Apr 3 '12 at 22:40
1  
It's impossible for the Row Count task to be faster than a simple COUNT(*) at the source. –  Gonsalu Apr 4 '12 at 9:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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