SSIS OLE DB Source No Columns Displayed Problem

Hi SSIS Experts

I have a problem in that I execute the following code within a OLE DB Source to a SQL 2k database. The results are returned when I press the Preview button however when I open the Columns tab I do not get results returned.

As you will see from my code I have tried to use both a table var & # table both produce the same results.

Any Solutions to this more then welcome



set nocount on

declare @l_Table_name varchar(255)
,@l_cmd varchar(4000)
,@l_db_name varchar(255)

Declare @result table (
SQLInstanceName varchar(255)
,DatabaseName varchar(255)
,TableName varchar(255)
,RecordDate datetime
)

-- Create temp table to hold Table data
create table #TableList
(
SQLInstanceName varchar(255)
,DatabaseName varchar(255)
,TableName varchar(255)
)

-- Load list of databases from master into Cursor
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases where name <> 'Tempdb' -- Exclude Tempdb

open db_name_cursor

fetch next from db_name_cursor into
@l_db_name

While (@@fetch_status = 0)
begin



-- Build select statment to be executed on each database.
set @l_cmd = 'use ' + @l_db_name
set @l_cmd = @l_cmd + ' insert into #TableList (SQLInstanceName,DatabaseName,TableName) '
set @l_cmd = @l_cmd + ' select @@servername SQLInstanceName,db_name(), name from sysobjects WHERE type = ''U'''

-- Exec the command
exec (@l_cmd)

--print @l_cmd


fetch next from db_name_cursor into
@l_db_name
end

-- Clean up Cursor
close db_name_cursor
deallocate db_name_cursor



insert into @Result
select *,getdate() RecordDate from #TableList

drop table #TableList
set nocount off

Select * from @result