Please answer the following: You use no input parameters mapping, right? How do you consume the output/result? Now, no errors executing the SQL in BIDS (turns green), too? What do you get in SSMS?Arthur My Blog

Hi, I have some SQL embedded within a OLE DB Data Source component. [Data access mode] has been set to [SQL command]. The SQL has three variables defined using the DECLARE command and values assigned using the SET command. If I preview the query results I get a sample of 200 rows. The SQL also works as expected when run in SSMS. All good so far. However, when I execute the package no rows are returned. It appears to be something to do with the SQL variables as the first SQL statement does not return results, where as the second one does. Not sure why this is the case? Can anyone shed some light on what I am missing or doing wrong? Thanks SQL #1 - original SQL statement - not returning rows in SSIS DECLARE @Working_Hours_In_A_Day AS FLOAT DECLARE @START_DATE AS DATETIME DECLARE @END_DATE AS DATETIME SET @Working_Hours_In_A_Day = 7.5 SET @START_DATE = (SELECT MIN(Assignment_Start_Date) FROM ESR_STAFF_IN_POST) SET @END_DATE = GETDATE() SELECT [Employee_Number] ,[Role] ,[Position_Title] ,[Assignment_Category] ,[Assignment_Status] ,[Employee_Category] ,[Ethnic_Origin] ,[Staff_Group] ,[Organisation] ,D.[date] AS Last_Day_Of_Month ,[FTE] ,@Working_Hours_In_A_Day AS [Working_Hours_In_A_Day] ,FTE * DAY(D.[Date]) AS FTE_Days_In_Month ,(FTE * @Working_Hours_In_A_Day) * DAY(D.[date]) AS FTE_Hours_In_Month FROM ESR_STAFF_IN_POST CROSS JOIN ESR_DW.DIM.DATE AS D WHERE D.is_month_last_day = 1 AND D.[date] BETWEEN @START_DATE AND @END_DATE SQL #2 - revised SQL statement - returns rows in SSIS DECLARE @Working_Hours_In_A_Day AS FLOAT -- DECLARE @START_DATE AS DATETIME -- DECLARE @END_DATE AS DATETIME SET @Working_Hours_In_A_Day = 7.5 -- SET @START_DATE = (SELECT MIN(Assignment_Start_Date) FROM ESR_STAFF_IN_POST) -- SET @END_DATE = GETDATE() SELECT [Employee_Number] ,[Role] ,[Position_Title] ,[Assignment_Category] ,[Assignment_Status] ,[Employee_Category] ,[Ethnic_Origin] ,[Staff_Group] ,[Organisation] ,D.[date] AS Last_Day_Of_Month ,[FTE] ,@Working_Hours_In_A_Day AS [Working_Hours_In_A_Day] ,FTE * DAY(D.[Date]) AS FTE_Days_In_Month ,(FTE * @Working_Hours_In_A_Day) * DAY(D.[date]) AS FTE_Hours_In_Month FROM ESR_STAFF_IN_POST CROSS JOIN ESR_DW.DIM.DATE AS D WHERE D.is_month_last_day = 1 AND D.[date] BETWEEN (SELECT MIN(Assignment_Start_Date) FROM ESR_STAFF_IN_POST) AND GETDATE() --@START_DATE AND @END_DATE Mo