Integration Services Package - DimRelativeDayLoad

Property	Value
Name	DimRelativeDayLoad
Description	This package will load data to the DimRelativeDay dimension AND populates the ETL working temp table DimRelativeDay_CopyFromDW. The package retrieves term data from the DWStaging source table. This data is then used to update and insert new records in DimRelativeDay. After the dimension is loaded, the DWOperations table DimRelativeDay_CopyFromDW is emptied and loaded from the dimension.
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	e93f47c3-c339-4b8d-b458-554be8af2a95
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	145
Version Comments
Version Guid	d48db807-d222-44df-b13b-8030ade9314e
Version Major	1
Version Minor	0

Name	Type	Description
DFT Load DimRelativeDay_CopyFromDW from DimRelativeDay	Data Flow Task	Data Flow Task
DFT Load New & Update Temp Tables from Temp DimRelativeDay Table	Data Flow Task	Data Flow Task
DFT Load Staged Term Session Temp Table from Staging	Data Flow Task	Data Flow Task
DFT Load Temp Staged Term from Staging	Data Flow Task	Data Flow Task
SQL DimRelativeDay Update Fields	Task	Execute SQL Task
SQL Empty DimRelativeDay_CopyFromDW	Task	Execute SQL Task
SQL Empty Temp Relative Day Tables	Task	Execute SQL Task
SQL Load New Rows into DimRelativeDay	Task	Execute SQL Task
SQL Load SCD1 Rows into DimRelativeDay	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::NewInsertDimRelativeDay	/* SQL INSERT NEW Rows into DimRelativeDay from DWOperations Temp New Table */ INSERT INTO [EnterpriseDW].[dbo].[DimRelativeDay] ( RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,[RelativeDayFullDescription_Sort] ,[RelativeDayFullDescription] ) SELECT RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,0 ,'N/A' FROM dbo.Staged_dbo_DimRelativeDay__New	" /* SQL INSERT NEW Rows into DimRelativeDay from DWOperations Temp New Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimRelativeDay] ( RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,[RelativeDayFullDescription_Sort] ,[RelativeDayFullDescription] ) SELECT RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,0 ,'N/A' FROM dbo.Staged_dbo_DimRelativeDay__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::SCD1UpdateDimRelativeDay	/* UPDATE SCD1 in DimRelativeDay from DWOperations SCD1 Temp Table */ UPDATE Dim SET TermYear = SCD1.TermYear , TermTypeCode = SCD1.TermTypeCode , TermTitle = SCD1.TermTitle , RelativeDay = SCD1.RelativeDay , RelativeDayDate = SCD1.RelativeDayDate , RelativeDayDateSK = SCD1.RelativeDayDateSK FROM [EnterpriseDW].[dbo].[DimRelativeDay] as Dim INNER JOIN dbo.Staged_dbo_DimRelativeDay__Type1Update SCD1 ON Dim.Term = SCD1.Term and Dim.RelativeDayNumber = SCD1.RelativeDayNumber	" /* UPDATE SCD1 in DimRelativeDay from DWOperations SCD1 Temp Table */ UPDATE Dim SET TermYear = SCD1.TermYear , TermTypeCode = SCD1.TermTypeCode , TermTitle = SCD1.TermTitle , RelativeDay = SCD1.RelativeDay , RelativeDayDate = SCD1.RelativeDayDate , RelativeDayDateSK = SCD1.RelativeDayDateSK FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimRelativeDay] as Dim INNER JOIN dbo.Staged_dbo_DimRelativeDay__Type1Update SCD1 ON Dim.Term = SCD1.Term and Dim.RelativeDayNumber = SCD1.RelativeDayNumber "	This variable contains the SQL statement that is used to update the changed rows in the DimTerm table
User::SQLRelativeDaySource	/* Primary SELECT for the Relative Day Dimension / SELECT CAST( CASE WHEN RelDay.SeqNumber < 0 THEN ((LEFT(Term.TRM_YR,4) 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * -1 * 1000000)) * -1 ELSE (LEFT(Term.TRM_YR,4) * 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * 1000000) END AS INT ) AS SRCRelativeDaySK ,LTRIM(RTRIM(CAST(Term.TRM_YR AS varchar(6)))) AS SRCTerm ,CAST(LEFT(Term.TRM_YR,4) AS varchar(4)) AS SRCTermYear ,CAST(SUBSTRING(Term.TRM_YR,5,1) AS varchar(3)) AS SRCTermTypeCode ,LTRIM(RTRIM(CAST(Term.TRM_TTL AS varchar(30)))) AS SRCTermTitle ,CAST(RelDay.SeqNumber AS INT) AS SRCRelativeDayNumber ,'Day ' + CAST(RelDay.SeqNumber AS varchar(11)) AS SRCRelativeDay ,CAST(dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber AS DATE) AS SRCRelativeDayDate ,CONVERT (varchar(8),dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber,112) AS SRCRelativeDayDateSK /* Term and Session information exists in the Staged TermSession Table / FROM dbo.Staged_TermSessionTempTable Term / Cross Join with the Sequential Numbers Table / CROSS JOIN Lookup_SequentialNbrs RelDay / Select the two years of Relative Days / WHERE (RelDay.SeqNumber >= -365 and RelDay.SeqNumber <= 365) / Order by Term and Relative Day number for processing */ ORDER BY Term.TRM_YR ,RelDay.SeqNumber	"/* Primary SELECT for the Relative Day Dimension / SELECT CAST( CASE WHEN RelDay.SeqNumber < 0 THEN ((LEFT(Term.TRM_YR,4) 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * -1 * 1000000)) * -1 ELSE (LEFT(Term.TRM_YR,4) * 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * 1000000) END AS INT ) AS SRCRelativeDaySK ,LTRIM(RTRIM(CAST(Term.TRM_YR AS varchar(6)))) AS SRCTerm ,CAST(LEFT(Term.TRM_YR,4) AS varchar(4)) AS SRCTermYear ,CAST(SUBSTRING(Term.TRM_YR,5,1) AS varchar(3)) AS SRCTermTypeCode ,LTRIM(RTRIM(CAST(Term.TRM_TTL AS varchar(30)))) AS SRCTermTitle ,CAST(RelDay.SeqNumber AS INT) AS SRCRelativeDayNumber ,'Day ' + CAST(RelDay.SeqNumber AS varchar(11)) AS SRCRelativeDay ,CAST(dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber AS DATE) AS SRCRelativeDayDate ,CONVERT (varchar(8),dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber,112) AS SRCRelativeDayDateSK /* T	This variable contain the SQL statement to extract the data for the Term dimension from the source system.

Name

Value

Expression

Description