use variable table in OLE DB source
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