|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | FactEnrollmentActivityLoad |
| 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 | 66d05766-a264-4793-80ac-d20524527e4e |
| 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 | 291 |
| Version Comments | |
| Version Guid | 9ee3818e-bf0f-4c53-bf5d-10e04e2cf08b |
| 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 |
|
Task |
Execute SQL Task |
|
Task |
Execute SQL 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 add and drop log records between the begin and end dates supplied to this routine.
SELECT (CASE
WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID
WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID
ELSE '99999'
END) AS STDNT_ID
,[EFF_TRM] AS EFF_TRM
,[REF_NUM] AS REF_NUM
,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate
,(CASE
WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action
ELSE 0
END) as DropAction
,(CASE
WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action
ELSE 0
END) as AddAction
FROM [dbo].[ST_STDNT_CLS_LOG] AS ClsLog
LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId
ON ClsLog.STDNT_ID = StudentId.STUDENT_ID
LEFT JOIN
( SELECT PrevStudentId.PREV_STDNT_ID
,Student1.STUDENT_ID
FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId
JOIN ST_STDNT_A AS Student1
ON Student1.ISN_ST_STDNT_A = PrevStudentId.ISN_ST_STDNT_A
) PrevStudentLook
ON ClsLog.STDNT_ID = PrevStudentLook.PREV_STDNT_ID
WHERE (([LOG_ACTION] = 'A' AND [TRNSCTN_TY] = 'A')
OR ([LOG_ACTION] = 'M' AND [TRNSCTN_TY] = 'D'))
AND [REF_NUM] IS NOT NULL
AND dbo.udf_StringToDate([LOG_DATE]) >= 'Jan 1 1900 12:00AM'
AND dbo.udf_StringToDate([LOG_DATE]) <= 'Mar 4 2016 10:33AM'
|
"
-- Get add and drop log records between the begin and end dates supplied to this routine.
SELECT (CASE
WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID
WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID
ELSE '99999'
END) AS STDNT_ID
,[EFF_TRM] AS EFF_TRM
,[REF_NUM] AS REF_NUM
,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate
,(CASE
WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action
ELSE 0
END) as DropAction
,(CASE
WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action
ELSE 0
END) as AddAction
FROM [dbo].[ST_STDNT_CLS_LOG] AS ClsLog
LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId
ON ClsLog.STDNT_ID = StudentId.STUDENT_ID
LEFT JOIN
( SELECT PrevStudentId.PREV_STDNT_ID
,Student1.STUDENT_ID
FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId
JOIN ST_STDNT_A AS Student1
|
||
SELECT (CASE
WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID
WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID
ELSE '99999'
END) AS STDNT_ID
,[EFF_TRM] AS EFF_TRM
,[REF_NUM] AS REF_NUM
,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate
,(CASE
WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action
ELSE 0
END) as DropAction
,(CASE
WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action
ELSE 0
END) as AddAction
FROM [dbo].[ST_STDNT_CLS_LOG_ARCH_A] AS ClsLog
LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId
ON ClsLog.STDNT_ID = StudentId.STUDENT_ID
LEFT JOIN
( SELECT PrevStudentId.PREV_STDNT_ID
,Student1.STUDENT_ID
FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId
JOIN ST_STDNT_A AS Student1
ON Student1.ISN_ST_STDNT_A = PrevStudentId.ISN_ST_STDNT_A
) PrevStudentLook
ON ClsLog.STDNT_ID = PrevStudentLook.PREV_STDNT_ID
WHERE (([LOG_ACTION] = 'A' AND [TRNSCTN_TY] = 'A')
OR ([LOG_ACTION] = 'M' AND [TRNSCTN_TY] = 'D'))
AND [REF_NUM] IS NOT NULL
AND dbo.udf_StringToDate([LOG_DATE]) >= 'Jan 1 1900 12:00AM'
AND dbo.udf_StringToDate([LOG_DATE]) <= 'Mar 4 2016 10:33AM'
|
"
SELECT (CASE
WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID
WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID
ELSE '99999'
END) AS STDNT_ID
,[EFF_TRM] AS EFF_TRM
,[REF_NUM] AS REF_NUM
,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate
,(CASE
WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action
ELSE 0
END) as DropAction
,(CASE
WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action
ELSE 0
END) as AddAction
FROM [dbo].[ST_STDNT_CLS_LOG_ARCH_A] AS ClsLog
LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId
ON ClsLog.STDNT_ID = StudentId.STUDENT_ID
LEFT JOIN
( SELECT PrevStudentId.PREV_STDNT_ID
,Student1.STUDENT_ID
FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId
JOIN ST_STDNT_A AS Student1
ON Student1.ISN_ST_STDNT_A = PrevStudentId.ISN_ST_STDNT_A
) PrevStudentLoo |
||
Jan 1 1900 12:00AM |
|||
Mar 4 2016 10:33AM |
|||
Y |
|||
UPDATE [EnterpriseDW].dbo.FactEnrollmentActivity
SET StudentSK = obs_std.NewStudentSK
FROM [EnterpriseDW].dbo.FactEnrollmentActivity fct
INNER JOIN [EnterpriseDW].dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK)
WHERE ds.RowIsCurrent = 'Y';
|
"
UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity
SET StudentSK = obs_std.NewStudentSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity fct
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK)
WHERE ds.RowIsCurrent = 'Y';
"
|
||
UPDATE [EnterpriseDW].dbo.FactEnrollmentActivity
SET StudentTermSK = NEW_d_StdTrm.StudentTermSK
FROM [EnterpriseDW].dbo.FactEnrollmentActivity f_enract
INNER JOIN [EnterpriseDW].dbo.DimStudentTerm OLD_d_StdTrm
ON (OLD_d_StdTrm.StudentTermSK = f_enract.StudentTermSK)
INNER JOIN dbo.Obsolete_DimStudent obs_DimStd
ON (obs_DimStd.OldStudentIdAK = OLD_d_StdTrm.StudentId)
INNER JOIN [EnterpriseDW].dbo.DimStudentTerm NEW_d_StdTrm
ON (
NEW_d_StdTrm.StudentId = obs_DimStd.NewStudentIdAK AND
NEW_d_StdTrm.Term = OLD_d_StdTrm.Term
)
WHERE OLD_d_StdTrm.RowIsCurrent = 'Y'
AND NEW_d_StdTrm.RowIsCurrent = 'Y';
|
"UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity
SET StudentTermSK = NEW_d_StdTrm.StudentTermSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity f_enract
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimStudentTerm OLD_d_StdTrm
ON (OLD_d_StdTrm.StudentTermSK = f_enract.StudentTermSK)
INNER JOIN dbo.Obsolete_DimStudent obs_DimStd
ON (obs_DimStd.OldStudentIdAK = OLD_d_StdTrm.StudentId)
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimStudentTerm NEW_d_StdTrm
ON (
NEW_d_StdTrm.StudentId = obs_DimStd.NewStudentIdAK AND
NEW_d_StdTrm.Term = OLD_d_StdTrm.Term
)
WHERE OLD_d_StdTrm.RowIsCurrent = 'Y'
AND NEW_d |
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
