OLE DB Source - Data Access Mode - SQL Command

I got a package with data flow task. Within the data flow task I have flat file with Fiscal Calendar defined. I got another data source within the data flow task, which is OLE DB Source. I want to use SQL Command as data access mode. SQL similar to one the below is in there.

----------------------------------------------------------------------------------------------------

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
DECLARE @date DATETIME
DECLARE @id INT

SET @startdate = '1993-09-26' --Change these to
SET @enddate = '2010-09-25' --whatever you want
SET @id = 1
SET @date = DATEADD(dd, @id, @startdate)

WHILE @date <= @enddate
BEGIN
select @date CalendarDate,
DATEPART(dd, @date) CalendarDayMonth,
DATEPART(dy, @date) CalendarDayYear,
DATEPART(dw, @date) CalendarDayWeek,
DATENAME(dw, @date) CalendarDayName

SET @id = @id + 1
SET @date = DATEADD(dd, @id, @startdate)

END

----------------------------------------------------------------------------------------------------

This SQL works fine in SSMS and returns around 6000 rows. But when I plug the same SQL in OLE DB Source it returns only the first record. It is not going through the WHILE loop.

Has anyone came across this?

Thanks

Sutha