|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | FactStudentSuccess_TermSnapshotLoad |
| Description | This package loads Enrollment activity information (Drops, Adds, Credit Hours, FTE, etc.) for all Terms. |
| Checkpoint File Name | |
| Checkpoint Usage | Never |
| Check Signature On Load | True |
| Creation Date | Tuesday, May 08, 2012 4:11 PM |
| Creator Computer Name | SCOWAN |
| Creator Name | SCOWAN\hetgroup |
| 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 | 2db40a8a-e7fa-44fd-b9f7-99fc8e762f74 |
| 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 | 343 |
| Version Comments | |
| Version Guid | e5d80ad4-64bc-46e4-b65e-8da662cbdcb9 |
| Version Major | 1 |
| Version Minor | 0 |
Executables
| Name | Type | Description |
Data Flow Task |
Data Flow Task |
|
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 |
EnterpriseDW |
|||
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. |
||
/* Get all the End of Term records that have changed during the input date range */
SELECT
STDNT_ID AS StudentId
,TRM_YR AS TrackingTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,ISNULL(CUM_GPA,0) AS CumGPA
,ISNULL(CUM_GRD_PTS,0) AS CumGradePoints
,ISNULL(CUM_HRS_ERN,0) AS CumHoursEarned
,ISNULL(CUM_HRS_ATT,0) AS CumHoursAttempted
,ISNULL(CUM_GPA_HRS,0) AS CumGPAHours
,ISNULL(TRM_GPA,0) AS TermGPA
,ISNULL(TRM_GRD_PTS,0) AS TermGradePoints
,ISNULL(TRM_HRS_ERN,0) AS TermHoursEarned
,ISNULL(TRM_HRS_ATT,0) AS TermHoursAttempted
,ISNULL(TRM_GPA_HRS,0) AS TermGPAHours
,CASE
WHEN STDNT_ENROLLED_FLG = 'Y' THEN 1
ELSE 0
END AS EnrolledInTerm
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 1
ELSE 0
END AS GPA_None
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 0 /* No GPA */
WHEN ISNULL(CUM_GPA,0) < 2.0 THEN 1
ELSE 0
END AS GPA_000_199
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 2.0 AND 2.49 THEN 1
ELSE 0
END AS GPA_200_249
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 2.5 AND 2.99 THEN 1
ELSE 0
END AS GPA_250_299
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 3.0 AND 3.49 THEN 1
ELSE 0
END AS GPA_300_349
,CASE
WHEN ISNULL(CUM_GPA,0) >= 3.5 THEN 1
ELSE 0
END AS GPA_350_Plus
,CASE
WHEN DEGREE_LT25PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_LessThan25_Pct
,CASE
WHEN DEGREE_25PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_25_Pct
,CASE
WHEN DEGREE_50PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_50_Pct
,CASE
WHEN DEGREE_75PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_75_Pct
,CASE
WHEN DEGREE_100PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_100_Pct
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'C' THEN 1
ELSE 0
END AS SOAP_Clear
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'W' THEN 1
ELSE 0
END AS SOAP_Warning
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'A' THEN 1
ELSE 0
END AS SOAP_Appeal
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'S' THEN 1
ELSE 0
END AS SOAP_Suspension
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'P' THEN 1
ELSE 0
END AS SOAP_Probation
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'D' THEN 1
ELSE 0
END AS SOAP_Dismissal
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) NOT IN ('C','W','A','S','P','D') THEN 1
ELSE 0
END AS SOAP_Other
,CASE
WHEN STDNT_GRAD_PROGRESS = 'NE' THEN 1
ELSE 0
END AS GraduationProgress_NotEnrolledFor2Terms
,CASE
WHEN STDNT_GRAD_PROGRESS = 'EN' THEN 1
ELSE 0
END AS GraduationProgress_Continueing
,CASE
WHEN STDNT_GRAD_PROGRESS = 'AG' THEN 1
ELSE 0
END AS GraduationProgress_AppliedToGraduate
,CASE
WHEN STDNT_GRAD_PROGRESS = 'GR' THEN 1
ELSE 0
END AS GraduationProgress_Graduated
FROM ST_STDNT_ENDOFTERM_A AS EOT
OUTER APPLY (
SELECT TOP 1
(STDNT_ID + PGM_ID + EFF_TERM + RIGHT('000' + CAST(SEQ_NUM AS VARCHAR(3)), 3)) AS StudentObjectiveAK
FROM ST_STDNT_OBJ_AWD_A Obj
WHERE Obj.STDNT_ID IS NOT NULL AND Obj.PGM_ID IS NOT NULL AND Obj.EFF_TERM IS NOT NULL
AND Obj.STDNT_ID = EOT.STDNT_ID
AND Obj.PGM_ID = EOT.PGM_ID
AND Obj.EFF_TERM <= EOT.TRM_YR
ORDER BY Obj.EFF_TERM DESC, Obj.SEQ_NUM DESC
) PgmObj
|
"
/* Get all the End of Term records that have changed during the input date range */
SELECT
STDNT_ID AS StudentId
,TRM_YR AS TrackingTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,ISNULL(CUM_GPA,0) AS CumGPA
,ISNULL(CUM_GRD_PTS,0) AS CumGradePoints
,ISNULL(CUM_HRS_ERN,0) AS CumHoursEarned
,ISNULL(CUM_HRS_ATT,0) AS CumHoursAttempted
,ISNULL(CUM_GPA_HRS,0) AS CumGPAHours
,ISNULL(TRM_GPA,0) AS TermGPA
,ISNULL(TRM_GRD_PTS,0) AS TermGradePoints
,ISNULL(TRM_HRS_ERN,0) AS TermHoursEarned
,ISNULL(TRM_HRS_ATT,0) AS TermHoursAttempted
,ISNULL(TRM_GPA_HRS,0) AS TermGPAHours
,CASE
WHEN STDNT_ENROLLED_FLG = 'Y' THEN 1
ELSE 0
END AS EnrolledInTerm
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 1
ELSE 0
END AS GPA_None
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 0 /* No GPA */
WHEN ISNULL(CUM_GPA,0) < 2.0 THEN 1
ELSE 0
END AS GPA_000_199
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 2.0 AND 2.49 THEN 1
EL |
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
