DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM'SELECT CAST(CASE WHEN COUNT(*) > 0 Then 'Y' ELSE 'N' END AS char(1)) AS Result FROM UTL_CODE_TABLE UtlCode WHERE (UtlCode.ISN_UTL_CODE_TABLE) IN ( /* Select changed records from GL Trans File */ SELECT ISN_UTL_CODE_TABLE FROM UTL_CODE_TABLE WHERE ( UTL_CODE_TABLE.RowUpdatedOn >= @IncrementalBeginDtTm AND UTL_CODE_TABLE.RowUpdatedOn <= @IncrementalEndDtTm AND UTL_CODE_TABLE.TABLE_NAME = 'DWCUSTTYPE' ) )

"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'SELECT CAST(CASE WHEN COUNT(*) > 0 Then 'Y' ELSE 'N' END AS char(1)) AS Result FROM UTL_CODE_TABLE UtlCode WHERE (UtlCode.ISN_UTL_CODE_TABLE) IN ( /* Select changed records from GL Trans File */ SELECT ISN_UTL_CODE_TABLE FROM UTL_CODE_TABLE WHERE ( UTL_CODE_TABLE.RowUpdatedOn >= @IncrementalBeginDtTm AND UTL_CODE_TABLE.RowUpdatedOn <= @IncrementalEndDtTm AND UTL_CODE_TABLE.TABLE_NAME = 'DWCUSTTYPE' ) ) "

DELETE FROM [EnterpriseDW].[dbo].[FactStudentTotalFeesForClass] WHERE StudentSK IN ( SELECT OldStudentSK FROM Obsolete_DimStudent )

"DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[FactStudentTotalFeesForClass] WHERE StudentSK IN ( SELECT OldStudentSK FROM Obsolete_DimStudent ) "

This is the SQL Statement that deletes the Fact Information that existed for Students processed in the Student ID Merge.

DELETE FROM [EnterpriseDW].[dbo].[FactStudentTotalFeesForClass] WHERE StudentTermSK IN ( SELECT FactPaid.StudentTermSK FROM FactStudentTotalFeesForClass_Staging StgeFact INNER JOIN [EnterpriseDW].[dbo].[DimStudentTerm] StTerm ON ( StgeFact.StudentTermAK = StTerm.StudentTermAK ) INNER JOIN [EnterpriseDW].[dbo].[FactStudentTotalFeesForClass] FactPaid ON ( FactPaid.StudentTermSK = StTerm.StudentTermSK ) )

"DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[FactStudentTotalFeesForClass] WHERE StudentTermSK IN ( SELECT FactPaid.StudentTermSK FROM FactStudentTotalFeesForClass_Staging StgeFact INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudentTerm] StTerm ON ( StgeFact.StudentTermAK = StTerm.StudentTermAK ) INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[FactStudentTotalFeesForClass] FactPaid ON ( FactPaid.StudentTermSK = StTerm.StudentTermSK ) ) "

This is the SQL Statement that will delete Fact records that will be reloaded.

DELETE FROM [EnterpriseDW] .[dbo].[FactStudentTotalFeesForClass] WHERE StudentTermSK IN ( SELECT StudentTermSK FROM [dbo].[Obsolete_StudentFees] obs )

" DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "] .[dbo].[FactStudentTotalFeesForClass] WHERE StudentTermSK IN ( SELECT StudentTermSK FROM [dbo].[Obsolete_StudentFees] obs ) "

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.

exec usp_FactStudentTotalFeesForClass_Select 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM'

"exec usp_FactStudentTotalFeesForClass_Select '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "'"

This is the SQL statement to extract all the Student Class Fee Data from the Source System.

exec usp_FactStudentTotalFeesForClassArchived_Select 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM'

"exec usp_FactStudentTotalFeesForClassArchived_Select '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "'"

This is the SQL statement to extract all the archived Student Class Fee Data from the Source System.

Jan 1 1900 12:00AM

The IncrementalBeginDtTm setting will contain the start date and time for which updates will be extracted. This is a configuration setting from SSIS_Configurations that are set in the Control Package.

Mar 4 2016 10:33AM

The IncrementalEndDtTm setting will contain the End Date and Time for which updates will be extracted. These values will be compared against the RowUpdatedOn column on the tables in the DWStaging database to determine which rows to extract and load into the Enterprise Data Warehouse.

Y

This SSIS_Configuration Setting will determine if a Full Load will be done or not.