ObsoleteDimStudentLoad
 ObsoleteDimStudentLoad (Variables)
  User Defined Variables
Name Value Expression Description
Configuration setting from SSIS_Configuration to specify the domain where the e-mail server is running.
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.
/* SELECT Students who have been Merged for Delete Processing */ SELECT prev.PREV_STDNT_ID AS OLD_ID ,stdnt.STUDENT_ID AS NEW_ID FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] prev /* Get Student Information */ LEFT OUTER JOIN [dbo].[ST_STDNT_A] stdnt ON ( stdnt.ISN_ST_STDNT_A = prev.ISN_ST_STDNT_A ) /* Get Old Student Information */ LEFT OUTER JOIN [dbo].[ST_STDNT_A] oldstdnt ON ( prev.PREV_STDNT_ID = oldstdnt.STUDENT_ID ) /* Where the student-id was changed within the begin and end date range and the OLD student does not still exist on ST_STDNT_A */ WHERE ( cast(prev.[CHG_ID_DATE] as date) >= cast('Jan 1 1900 12:00AM' as date) AND cast(prev.[CHG_ID_DATE] as date) <= cast('Mar 4 2016 10:33AM' as date) ) AND ( oldstdnt.STUDENT_ID IS NULL )
" /* SELECT Students who have been Merged for Delete Processing */ SELECT prev.PREV_STDNT_ID AS OLD_ID ,stdnt.STUDENT_ID AS NEW_ID FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] prev /* Get Student Information */ LEFT OUTER JOIN [dbo].[ST_STDNT_A] stdnt ON ( stdnt.ISN_ST_STDNT_A = prev.ISN_ST_STDNT_A ) /* Get Old Student Information */ LEFT OUTER JOIN [dbo].[ST_STDNT_A] oldstdnt ON ( prev.PREV_STDNT_ID = oldstdnt.STUDENT_ID ) /* Where the student-id was changed within the begin and end date range and the OLD student does not still exist on ST_STDNT_A */ WHERE ( cast(prev.[CHG_ID_DATE] as date) >= cast('" + @[User::IncrementalBeginDtTm] + "' as date) AND cast(prev.[CHG_ID_DATE] as date) <= cast('" + @[User::IncrementalEndDtTm] + "' as date) ) AND ( oldstdnt.STUDENT_ID IS NULL ) "
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.
/* Remove rows from Obsolete_DimStudent when Old and New Student ID are Equal */ DELETE FROM dbo.Obsolete_DimStudent WHERE OldStudentIdAK = NewStudentIdAK
" /* Remove rows from Obsolete_DimStudent when Old and New Student ID are Equal */ DELETE FROM dbo.Obsolete_DimStudent WHERE OldStudentIdAK = NewStudentIdAK "
  System Variables
Name Value Expression Description
Powered by BI Documenter