|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | ObsoleteDimStudentLoad |
| Description | This package will process any Student and related records that were deleted due to a Student Id Change/Merge process. The first step in this package is to truncate all the working tables that track which Student records will be deleted. The next step will read all the rows in the DimStudent table and check if a matching record is found in the DWStaging ST_STDNT_A table. If a match is not found, the record will be written to a working table. The records in the working table will be used by other SSIS packages to remove the associated Fact Records and related Student Dimensions. |
| 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 | 257484df-d83c-461d-b512-10e961691251 |
| 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 | 256 |
| Version Comments | |
| Version Guid | 4a13b722-826d-4002-b8b8-7368a8ebae37 |
| Version Major | 1 |
| Version Minor | 0 |
Executables
| Name | Type | Description |
Data Flow Task |
Data Flow Task |
|
Task |
Execute SQL Task |
|
Task |
Execute SQL Task |
Connection Managers
| Name | Description |
Log Providers
| Name | Description |
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 |
