SQL Server Integration Services 2008 OLE DB Source

Question

I have this query:

declare @company as varchar (20); declare @query as varchar (500); select @company=Name from Company; set @query='SELECT [Name] ,[Address] FROM [' + @company + '$Customer]'

exec (@query)

It can run succesfully in SQL Server and 8 rows returned, but when i copied that query and pasted it in SSIS OLE DB Source,it showed a warning message "No column information was return by the SQL command"

Is it because SSIS OLE DB can't execute more than one select command like in these:

select @company=Name from Company

SELECT [Name] ,[Address] FROM [' + @company + '$Customer]

Please advise

Thanks

Answer 1

I would guess that this is because you're looking for a multiple number of rowsets, from a$Customer, b$Customer, etc, right? The OLEDB source will provide just one rowset, as far as I know.

One solution would be to use a Script component as a source; in there, you would perform your first query to get the list of companies, loop through it, and fetch the list of name/address records for each, adding the whole lot to the output.

Simpler might be an Execute SQL Task in the control flow, where you get the list of company tables. Then a foreach to shred it, with the dataflow dealing with each table one at a time. You'd set a variable in the foreach, and use that to build dynamic SQL in the dataflow source component.

Cheers, Geoff

EDIT: in response to:

I forgot to inform you that this select statement "select @company=Name from Company" only returns 1 row.

Since you only have one record in the Company table, just select it into a variable using an Execute SQL Task, then in your data flow set your OLEDB source to 'SQL command from variable'. You'll need a variable to hold the constructed SQL command; it should work then.

Answer 2

If you are using the first SELECT statement to set a variable value, then it won't cause a problem even though there are multiple select statements. I just ran the following test code:

RUN BEFORE BUILDING THE DATA FLOW

CREATE TABLE dbo.Company (Name VARCHAR(20) NOT NULL, Address VARCHAR(20) NULL)
INSERT INTO dbo.Company (Name, Address)
SELECT 'Test A', 'A'
UNION
SELECT 'Test B', 'B'
UNION
SELECT 'Test C', 'C'
UNION
SELECT 'Test D', 'D'
UNION
SELECT 'Test E', 'E'
UNION
SELECT 'Test F', 'F'
UNION
SELECT 'Test G', 'G'
UNION
SELECT 'Test H', 'H'

RUN IN THE DATA FLOW OLE DB SOURCE

declare @company as varchar (20); 
declare @query as nvarchar (500); 

select @company = Name 
from dbo.Company; 

set @query = 'SELECT [Name] ,[Address] FROM dbo.Company WHERE Name IN (''Test A'', ''Test B'')';

EXEC sp_executeSQL @query;

This outputted the correct 2 rows without any problem. Are you sure your data flow code is correct? Are you trying to use SSIS package variables in your SQL code?