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

Hey,

OLEDB Source connection to what database?

Brian

|||

I am connecting to my warehouse DB but the source is just the SQL, it doesn't need to extract anything from DB, as the SQL should give the result set.

What I should ideally use is "Execute SQL Task", which is in Control Flow Task.

Maybe I could achieve this by putting into a temp table and source it from the temp table. I am going to check it out.

Thanks

Sutha