use variable table in OLE DB source

Question

I try to export data to a flat file from OLE DB source. Here is what I do under OLE DB source editor. In SQL command Text:

declare @t 
   table (customerid varchar(100),[enrollment_Date] varchar(100))
insert into @t exec master.dbo.my_stored_procedure
select * from @t

In the preview, I can see data but when I execute it, I get empty text file. Do you know why?

Answer 1

use SET NOCOUNT ON

SET NOCOUNT ON
declare @t 
   table (customerid varchar(100),[enrollment_Date] varchar(100))
insert into @t exec master.dbo.my_stored_procedure
select * from @t

http://www.bidn.com/blogs/dustinryan/ssis/835/ssis-ole-db-source-using-table-variables-returns-no-rows