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'