Integration Services Package

Property	Value
Name	DimTermLoad
Description	This package will load any change to the DimTerm dimension. The first step in this package is to truncate all the working tables needed to track new and updated records. The next step will load the data into a staged table in DWOperations and then update the Relative Day information on the dimension based on the current day for which the package is executed. The data in the Staged Table will be compared against that in the dimension and will be inserted or updated as required.
Checkpoint File Name
Checkpoint Usage	Never
Check Signature On Load	True
Creation Date	Tuesday, May 08, 2012 4:11 PM
Creator Computer Name	HETGROUP-PC
Creator Name	hetgroup-PC\hstoddar
Debug Mode	False
Delay Validation	False
Disable	False
Disable Event Handlers	False
Enable Configurations	True
Encrypt Checkpoints	False
Fail Package On Failure	False
Fail Parent On Failure	False
Forced Execution Value	0
Force Execution Value	None
Description	False
ID	b9f8ca12-a16b-4873-8200-f16cecedc41d
Interactive Mode	False
Is Default Locale ID	False
Isolation Level	Serializable
Locale ID	1033
Logging Mode	Enabled
Max Concurrent Executables	-1
Maximum Error Count	1
Offline Mode	False
Package Type	DTSDesigner100
Protection Level	DontSaveSensitive
Save Checkpoints	False
Suppress Configuration Warnings	False
Suspend Required	False
Transaction Option	Supported
Update Objects	False
Version Build	112
Version Comments
Version Guid	42066907-ae57-41b2-be8c-75c7db2de1b5
Version Major	1
Version Minor	0

Name	Type	Description
DFT Load Temp Staged Term from Staging	Data Flow Task	Data Flow Task
DFT Load Temp Term Tables from Staging	Data Flow Task	Data Flow Task
SQL Empty Temp Term Tables	Task	Execute SQL Task
SQL Load New Rows into Dim in Operations	Task	Execute SQL Task
SQL Load SCD1 Rows into Dim in Operations	Task	Execute SQL Task
SQL Update Relative Day	Task	Execute SQL Task

Name	Description
DWOperations
DWStaging
EnterpriseDW

Name	Description

Name	Value	Expression	Description
User::CurrentDayDate	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.
User::EDWDBName	EnterpriseDW		Configuration setting in SSIS_Configurations that specifies the name of the Enterprise Data Warehouse.
User::EDWLinkedServerName	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.
User::NewInsertDimTerm	/* 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.
User::OperationsDBName	DWOperations		Configuration setting in SSIS_Configurations that specifies the name of the Operations Database.
User::SCD1UpdateDimTerm	/* 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
User::SQLTermSource	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.
User::SQLUpdateRelativeDay	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.

Name

Value

Expression

Description