Task

Execute SQL Task

2016-03-04

This variable is set to the current date if the ETL process is executed after 6 am. If the ETL is executed prior to 6 am, it will be set to yesterday?s date. This variable will be used to extract the Enrollment information for that date. If the UseManualDates is set to Y, this variable will not be updated.

EnterpriseDW

N/A

DECLARE @CurrentDay date = '2016-03-04' DECLARE @FiscalYear varchar(7) DECLARE @FiscalYearStart varchar(7) DECLARE @Year int DECLARE @TempDate date SET @Year = YEAR(@CurrentDay) SET @TempDate = ( CASE WHEN MONTH(@CurrentDay) = 7 THEN DATEADD(mm, -1,@CurrentDay) ELSE @CurrentDay END ) -- If we are in July, set the Fiscal Year to prior Fiscal Year since it is the last closed period SET @FiscalYear = (SELECT FiscalYear FROM [EnterpriseDW].[dbo].[DimDate] WHERE FullDate = @TempDate) SET @Year = CAST(SUBSTRING(@FiscalYear,4,4) as int) SET @Year = @Year - 15 SET @FiscalYearStart = 'FY' + ' ' + CAST(@Year as varchar(4)) UPDATE [EnterpriseDW].[dbo].[DimDate] SET [IsCurrentFiscalYear] = ( CASE WHEN FiscalYear <> @FiscalYear THEN 'N' ELSE 'Y' END ) FROM [DimDate] UPDATE [EnterpriseDW].[dbo].[DimDate] SET [IsAvailableForFinance] = 'Y' FROM [DimDate] WHERE FiscalYear >= @FiscalYearStart and FiscalYear <= @FiscalYear UPDATE [EnterpriseDW].[dbo].[DimDate] SET [CalendarPeriodSort] = (CASE WHEN ( MONTH(@CurrentDay) > 6 AND Year(DATEADD(yy, 1,@CurrentDay)) = CAST(RIGHT(FiscalYear,4) AS Int) OR Year(@CurrentDay) = CAST(RIGHT(FiscalYear,4) AS Int) ) AND MONTH(DATEADD(mm, -1,@CurrentDay)) = MonthOfYear THEN 1000000 + CalendarPeriod WHEN ( MONTH(@CurrentDay) > 6 AND Year(DATEADD(yy, 1,@CurrentDay)) <> CAST(RIGHT(FiscalYear,4) AS Int) OR Year(@CurrentDay) <> CAST(RIGHT(FiscalYear,4) AS Int) ) AND 6 = MonthOfYear THEN 1000000 + CalendarPeriod ELSE 2000000 + CalendarPeriod END ) --Set IsCurrentDay to match value in DWOperations.dbo.[SSIS Configurations] Var_CurrentDayDate --(This is passed in to package using pacakge configurations) UPDATE dbo.DimDate SET IsCurrentDay = 'N' WHERE IsCurrentDay = 'Y' UPDATE dbo.DimDate SET IsCurrentDay = 'Y' WHERE FullDate = @CurrentDay

"DECLARE @CurrentDay date = '" +@[User::CurrentDayDate] + "' DECLARE @FiscalYear varchar(7) DECLARE @FiscalYearStart varchar(7) DECLARE @Year int DECLARE @TempDate date SET @Year = YEAR(@CurrentDay) SET @TempDate = ( CASE WHEN MONTH(@CurrentDay) = 7 THEN DATEADD(mm, -1,@CurrentDay) ELSE @CurrentDay END ) -- If we are in July, set the Fiscal Year to prior Fiscal Year since it is the last closed period SET @FiscalYear = (SELECT FiscalYear FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimDate] WHERE FullDate = @TempDate) SET @Year = CAST(SUBSTRING(@FiscalYear,4,4) as int) SET @Year = @Year - 15 SET @FiscalYearStart = 'FY' + ' ' + CAST(@Year as varchar(4)) UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimDate] SET [IsCurrentFiscalYear] = ( CASE WHEN FiscalYear <

This is the variable that contains the SQL statement that is used to update DimDate dimension table with the correct information based on the CurrentDayDate variable.