2016-03-04

Configuration Setting from SSIS_Configuration. Contains the day for which the package will be executed. Will be used to calculate the Relative Day.

EnterpriseDW

Configuration setting in SSIS_Configurations that specifies the name of the Enterprise Data Warehouse.

N/A

If the Enterprise Data Warehouse resides on a different server from the DWStaging and DWoperations databases, a linked server must be defined and the name populated into the Var_EDWLinkedServername. If it resides on the same server, this setting must be set to ?N/A?. This is a configuration setting from SSIS_Configurations.

/* SQL INSERT into DimTerm on Operations from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimTerm] (TermSK , TermAK , TermStartDate , TermEndDate , TermTitle , AcademicYearCode , AcademicYear , TermYear , TermTypeCode , TermType , ActiveTerm , DefaultTerm , RelativeDay , PriorYearTerm , StateReportingTermYear , StateReportingTermSequence , RowIsCurrent , RowStartDate , RowEndDate ) SELECT TermSK , TermAK , TermStartDate , TermEndDate , TermTitle , AcademicYearCode , AcademicYear , TermYear , TermTypeCode , TermType , ActiveTerm , DefaultTerm , RelativeDay , PriorYearTerm , StateReportingTermYear , StateReportingTermSequence , RowIsCurrent , RowStartDate , RowEndDate FROM dbo.Staged_dbo_DimTerm__New

" /* SQL INSERT into DimTerm on Operations from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimTerm] (TermSK , TermAK , TermStartDate , TermEndDate , TermTitle , AcademicYearCode , AcademicYear , TermYear , TermTypeCode , TermType , ActiveTerm , DefaultTerm , RelativeDay , PriorYearTerm , StateReportingTermYear , StateReportingTermSequence , RowIsCurrent , RowStartDate , RowEndDate ) SELECT TermSK , TermAK , TermStartDate , TermEndDate , TermTitle , AcademicYearCode , AcademicYear , TermYear , TermTypeCode , TermType , ActiveTerm , DefaultTerm , RelativeDay , PriorYearTerm , StateReportingTermYear , StateReportingTermSequence , RowIsCurrent , RowStartDate , RowEndDate FROM dbo.Staged_dbo_DimTerm__New "

This variable contains the SQL statement that will be used to insert new rows into the DimTerm table.

DWOperations

Configuration setting in SSIS_Configurations that specifies the name of the Operations Database.

/* SQL UPDATE DimTerm from Temp Tables */ UPDATE Dim SET TermStartDate = SCD1.TermStartDate ,TermEndDate = SCD1.TermEndDate ,TermTitle = SCD1.TermTitle ,AcademicYearCode = SCD1.AcademicYearCode ,AcademicYear = SCD1.AcademicYear ,TermYear = SCD1.TermYear ,TermTypeCode = SCD1.TermTypeCode ,TermType = SCD1.TermType ,ActiveTerm = SCD1.ActiveTerm ,DefaultTerm = SCD1.DefaultTerm ,PriorYearTerm = SCD1.PriorYearTerm ,RelativeDay = SCD1.RelativeDay ,StateReportingTermYear = SCD1.StateReportingTermYear ,StateReportingTermSequence = SCD1.StateReportingTermSequence FROM [EnterpriseDW].[dbo].[DimTerm] as Dim INNER JOIN dbo.Staged_dbo_DimTerm__Type1Update SCD1 ON Dim.TermAK = SCD1.TermAK

"/* SQL UPDATE DimTerm from Temp Tables */ UPDATE Dim SET TermStartDate = SCD1.TermStartDate ,TermEndDate = SCD1.TermEndDate ,TermTitle = SCD1.TermTitle ,AcademicYearCode = SCD1.AcademicYearCode ,AcademicYear = SCD1.AcademicYear ,TermYear = SCD1.TermYear ,TermTypeCode = SCD1.TermTypeCode ,TermType = SCD1.TermType ,ActiveTerm = SCD1.ActiveTerm ,DefaultTerm = SCD1.DefaultTerm ,PriorYearTerm = SCD1.PriorYearTerm ,RelativeDay = SCD1.RelativeDay ,StateReportingTermYear = SCD1.StateReportingTermYear ,StateReportingTermSequence = SCD1.StateReportingTermSequence FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimTerm] as Dim INNER JOIN dbo.Staged_dbo_DimTerm__Type1Update SCD1 ON Dim.TermAK = SCD1.TermAK "

This variable contains the SQL statement that is used to update the changed rows in the DimTerm table

DECLARE @CurrentDayDate datetime = '2016-03-04' SELECT CAST(Term.TRM_YR AS int) AS SRCTermSK ,CAST(Term.TRM_YR AS int) AS SRCTermAK ,dbo.udf_StringToDate(Sess.SESS_BEG_DT) AS SRCTermStartDate ,dbo.udf_StringToDate(Sess.SESS_END_DT) AS SRCTermEndDate ,Term.TRM_TTL AS SRCTermTitle ,LEFT(Term.TRM_YR,4) AS SRCTermYear ,( CAST( CAST( LEFT(Term.TRM_YR,4) AS Int ) - 1 AS varchar(4) ) + SUBSTRING(Term.TRM_YR,5,1) ) AS SCRPriorYearTerm ,SUBSTRING(Term.TRM_YR,5,1) AS SRCTermTypeCode ,CASE WHEN SUBSTRING(Term.TRM_YR,5,1) = 1 THEN 'FALL TERM' WHEN SUBSTRING(Term.TRM_YR,5,1) = 2 THEN 'SPRING TERM' WHEN SUBSTRING(Term.TRM_YR,5,1) = 3 THEN 'SUMMER TERM' WHEN SUBSTRING(Term.TRM_YR,5,1) = 4 THEN 'SUM 2 TERM' ELSE 'UNKNOWN' END AS SRCTermType ,cal.CAL_TRNSCRPT_TTL AS SRCAcademicYearCode ,cal.CAL_TTL AS SRCAcademicYear ,CASE WHEN ( dbo.udf_StringToDate(SESS_BEG_DT) >= DATEADD(dd,-365, @CurrentDayDate) AND dbo.udf_StringToDate(SESS_BEG_DT) <= DATEADD(dd,365, @CurrentDayDate) ) THEN 'Yes' ELSE 'No' END AS SRCActiveTerm --,CAST(@CurrentDayDate - dbo.udf_StringToDate(SESS_BEG_DT) AS int) AS ActiveRelativeDay ,CASE WHEN CurrentTerm.Term_YR IS NULL THEN 'No' ELSE 'Yes' END AS SRCDefaultTerm , CAST(-999 as Int) AS SRCRelativeDay , CASE WHEN CAST(SUBSTRING(Term.TRM_YR,5,1) AS int) > 2 THEN CAST((CAST(LEFT(Term.TRM_YR,4) AS int) + 1) AS varchar(4)) ELSE LEFT(Term.TRM_YR,4) END as StateReportingTermYear , CASE WHEN CAST(SUBSTRING(Term.TRM_YR,5,1) AS int) > 2 THEN CAST(SUBSTRING(Term.TRM_YR,5,1) AS tinyint) - 2 WHEN NumTerms.TermCount = 4 THEN CAST(SUBSTRING(Term.TRM_YR,5,1) AS tinyint) + 2 ELSE CAST(SUBSTRING(Term.TRM_YR,5,1) AS tinyint) + 1 END as StateReportingTermSequence FROM ST_TERM_A Term INNER JOIN ST_SESSION_A Sess ON ( Term.TRM_YR = LEFT(Sess.SESSION_KEY,5) ) INNER JOIN ST_CALENDAR_A Cal ON ( LEFT (Term.TRM_YR,4) = cal.CAL_YR ) LEFT OUTER JOIN ( SELECT COUNT(*) as TermCount ,LEFT(TRM_YR,4) as TermYear FROM ST_TERM_A GROUP BY LEFT(TRM_YR,4) ) AS NumTerms ON ( NumTerms.TermYear = LEFT(Term.TRM_YR,4) ) LEFT OUTER JOIN ( SELECT TOP 1 CAST(Term.TRM_YR AS int) AS Term_YR --,ABS(CAST(@CurrentDayDate - dbo.udf_StringToDate(SESS_BEG_DT) AS int)) AS ABS_RelativeDay --,CAST(@CurrentDayDate - dbo.udf_StringToDate(SESS_BEG_DT) AS int) AS RelativeDay --SELECT COUNT (*) FROM ST_TERM_A Term INNER JOIN ST_SESSION_A Sess ON ( Term.TRM_YR = LEFT(Sess.SESSION_KEY,5) ) WHERE SUBSTRING (Sess.SESSION_KEY,7,4) = '1 ' ORDER BY ABS(CAST(@CurrentDayDate - dbo.udf_StringToDate(SESS_BEG_DT) AS int)) ,CAST(@CurrentDayDate - dbo.udf_StringToDate(SESS_BEG_DT) AS int) ) AS CurrentTerm ON (Term.TRM_YR = CurrentTerm.Term_YR) WHERE SUBSTRING (Sess.SESSION_KEY,7,4) = '1 ' ORDER BY CAST(Term.TRM_YR AS int)

" DECLARE @CurrentDayDate datetime = '" +@[User::CurrentDayDate]+ "' SELECT CAST(Term.TRM_YR AS int) AS SRCTermSK ,CAST(Term.TRM_YR AS int) AS SRCTermAK ,dbo.udf_StringToDate(Sess.SESS_BEG_DT) AS SRCTermStartDate ,dbo.udf_StringToDate(Sess.SESS_END_DT) AS SRCTermEndDate ,Term.TRM_TTL AS SRCTermTitle ,LEFT(Term.TRM_YR,4) AS SRCTermYear ,( CAST( CAST( LEFT(Term.TRM_YR,4) AS Int ) - 1 AS varchar(4) ) + SUBSTRING(Term.TRM_YR,5,1) ) AS SCRPriorYearTerm ,SUBSTRING(Term.TRM_YR,5,1) AS SRCTermTypeCode ,CASE WHEN SUBSTRING(Term.TRM_YR,5,1) = 1 THEN 'FALL TERM' WHEN SUBSTRING(Term.TRM_YR,5,1) = 2 THEN 'SPRING TERM' WHEN SUBSTRING(Term.TRM_YR,5,1) = 3 THEN 'SUMMER TERM' WHEN SUBSTRING(Term.TRM_YR,5,1) = 4 THEN 'SUM 2 TERM' ELSE 'UNKNOWN' END AS SRCTermType ,cal.CAL_TRNSCRPT_TTL AS SRCAcademicYearCode ,cal.CAL_TTL AS SRCAcademicYear ,CASE WHEN ( dbo.udf_StringToDate(SESS_BEG_DT) >= DATEADD

This variable contain the SQL statement to extract the data for the Term dimension from the source system.

DECLARE @CurrentDayDate datetime = '2016-03-04' UPDATE Staged_dbo_DimTerm SET RelativeDay = CAST(@CurrentDayDate - Staged_dbo_DimTerm.TermStartDate AS int) FROM Staged_dbo_DimTerm WHERE StagedTermSK <> -1

" DECLARE @CurrentDayDate datetime = '" + @[User::CurrentDayDate] + "' UPDATE Staged_dbo_DimTerm SET RelativeDay = CAST(@CurrentDayDate - Staged_dbo_DimTerm.TermStartDate AS int) FROM Staged_dbo_DimTerm WHERE StagedTermSK <> -1 "

This variable contains the SQL statement that will be used to update the relative day column in the Staged Dim Term table.