|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | FactStudentSuccess_FirstYearSnapshotLoad |
| 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 | 7471103a-fbc8-4f86-83bd-fe7b3b1ebf3a |
| 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 | 358 |
| Version Comments | |
| Version Guid | 61cb377c-b656-471e-b2f9-46b1069c0bfe |
| 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
EOT.STDNT_ID AS StudentId
,EOT.TRM_YR AS FirstYearTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 1
ELSE 0
END AS MeetsGPARequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 1
ELSE 0
END AS Meets24CreditsRequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) = 0 THEN 0
WHEN ISNULL(CUM_HRS_ATT,0) = 0 THEN 0
WHEN (CUM_HRS_ERN / CUM_HRS_ATT) * 100 >= 67 THEN 1
ELSE 0
END AS Meets67PctCompletedRequirement
,CASE
WHEN ISNULL(TAKEN_COLLEXPR_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedCollegeExpCourse
,CASE
WHEN ISNULL(TAKEN_GATEWAY_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedGatewayCourse
,CASE
WHEN Summer.SummerStudentId IS NOT NULL THEN 1
ELSE 0
END AS AttendedSummerTerm
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 0 /* Satisfied GPA */
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 0 /* Satisfied Credit Hours */
WHEN ISNULL(CUM_HRS_ATT,0) > 0 AND (ISNULL(CUM_HRS_ERN,0) / CUM_HRS_ATT) * 100 >= 67 THEN 0 /* Satisfied 67% Course Completion/Pass Rate */
WHEN ISNULL(TAKEN_COLLEXPR_COURSE,'N') = 'Y' THEN 0 /* Satisfied College Experience Course */
WHEN ISNULL(TAKEN_GATEWAY_COURSE,'N') = 'Y' THEN 0 /* Satisfied Gateway Course */
WHEN Summer.SummerStudentId IS NOT NULL THEN 0 /* Attended Summer Term */
ELSE 1
END AS DidNotMeetAny1stYearSuccessFactors
FROM ST_STDNT_ENDOFTERM_A AS EOT
/* Find the latest (term and seq num) student objective record for the specific student and program. */
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
/* Determine if the student attended a summer term and completed a course within the last year. */
/* If so, then the SummerStudentId field from this join will not be NULL. */
OUTER APPLY (
SELECT TOP 1
STDNT_ID AS SummerStudentId
FROM ST_STDNT_ENDOFTERM_A AS InsideSummer
WHERE InsideSummer.STDNT_ID IS NOT NULL AND InsideSummer.TRM_YR IS NOT NULL
AND InsideSummer.STDNT_ID = EOT.STDNT_ID
AND InsideSummer.TRM_YR > (CAST((CAST(SUBSTRING(EOT.TRM_YR,1,4) AS INT) - 1) AS VARCHAR(4)) + SUBSTRING(EOT.TRM_YR,5,1))
AND InsideSummer.TRM_YR <= EOT.TRM_YR
AND SUBSTRING(InsideSummer.TRM_YR,5,1) IN ('3','4') /* Summer Term */
AND ISNULL(InsideSummer.TRM_HRS_ERN,0) > 0 /* Attended = Completed a course */
) AS Summer |
"
/* Get all the End of Term records that have changed during the input date range */
SELECT
EOT.STDNT_ID AS StudentId
,EOT.TRM_YR AS FirstYearTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 1
ELSE 0
END AS MeetsGPARequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 1
ELSE 0
END AS Meets24CreditsRequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) = 0 THEN 0
WHEN ISNULL(CUM_HRS_ATT,0) = 0 THEN 0
WHEN (CUM_HRS_ERN / CUM_HRS_ATT) * 100 >= 67 THEN 1
ELSE 0
END AS Meets67PctCompletedRequirement
,CASE
WHEN ISNULL(TAKEN_COLLEXPR_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedCollegeExpCourse
,CASE
WHEN ISNULL(TAKEN_GATEWAY_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedGatewayCourse
,CASE
WHEN Summer.SummerStudentId IS NOT NULL THEN 1
ELSE 0
END AS AttendedSummerTerm
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 0 /* Satisfied GPA */
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 0 /* Sa |
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
