@table vs #table in SSIS

[mossbs]

Hi guys,

I have a query/stored procedure....

declare @t table
([ref] [int] IDENTITY(1,1) NOT NULL,
code varchar(100),logtime datetime,activity varchar(50))

declare @tt table
(ref int, code varchar(100),logtime datetime,activity varchar(50))

insert into @t
(code,logtime,activity)

select o.orgCode, min(f.logTime), f.message
from FactLog f
inner join DimOrganisation o on o.oid = f.dealer_id 
where f.entity__id = 131
group by orgCode,f.message
order by orgCode, min(f.logTime)

insert into @tt
(ref, code)
select min(ref), code
from @t
group by code


update tt
set tt.logtime = t.logtime,
tt.activity = t.activity
from @tt tt
inner join @t t
on t.ref = tt.ref

select * from @tt

the query runs fine in SSMS however when i stick this as a SQL command in a OLE DB Source in a SSIS package (with xl destination) all i get through are the column headers.

When trying to preview it in SSIS i get a 'Query Timeout expired' error - but assumed this was just as the query takes like a minute to run.

So... I thought i'd try it with #tables instead - however with this i get an error saying 'Unable to retreive column information from the data source'

any ideas what i am doing wrong here?

Cheers all.

 

[sparkbyte]

Not sure, but you might try posting this to the SSIS forum.

Just a thought though, have you tried it as a global variable??

either @@t or ##t



Thanks

John Fuhrman

 

[SimonSellick]

It might be to do with intermediate result sets being returned. Perhaps try starting the script with

set nocount on

 

[mossbs]

Simon, you are bang on.... that nocount thing gets me every time!

Cheers buddy.