|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | DimStudentObjectiveLoad |
| Description | This package will load any change to the DimStudentTerm dimension. The first step in this package is to truncate all the working tables needed to track new and updated records. The next step will load the data into a staged table in DWOperations. The data will be checked if there are any duplicate records and these will be flagged. The data in the Staged Table will be compared against that in the dimension and will be inserted or updated as required. If any duplicate records were found, a notification e-mail will be send to the appropriate staff |
| Checkpoint File Name | |
| Checkpoint Usage | Never |
| Check Signature On Load | True |
| Creation Date | Tuesday, May 08, 2012 4:11 PM |
| Creator Computer Name | ipeterson |
| Creator Name | ipeterso\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 | 21f6f886-cfd8-4b27-8016-74b869f3740e |
| 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 | 219 |
| Version Comments | |
| Version Guid | e1bef952-6f24-48f2-95c9-bd6e67f20cd9 |
| 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 |
Connection Managers
| Name | Description |
Log Providers
| Name | Description |
User Defined Variables
| Name | Value | Expression | Description |
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. |
||
exec usp_DimStudentObjective_Select 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM' |
"exec usp_DimStudentObjective_Select '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "'" |
This variable contains the execution string for the Stored Procedure that will retrieve the data from the source system. |
|
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. |
||
/* SQL INSERT into DimStudentObjective from New Temp Table */
INSERT INTO [EnterpriseDW].[dbo].[DimStudentObjective]
(
StudentObjectiveAK,
StudentId,
ProgramCode,
Program,
EffectiveTerm,
EffectiveTermTitle,
SequenceNumber,
AdmissionStatus,
AwardTypeCode,
AwardType,
PrimaryObjective,
ProgramStatusCode,
GraduationStatusCode,
ExpectedGraduationTerm,
ExpectedGraduationTermTitle,
ActualGraduationTerm,
ActualGraduationTermTitle,
GraduationAge,
GraduationAgeRange,
GraduationHonorsCode,
TransferMajorCode,
TransferMajor,
TransferInstitutionId,
TransferInstitution,
ReportedTerm,
ReportedTermTitle,
RowIsCurrent,
RowStartDate,
RowEndDate
)
SELECT
StudentObjectiveAK,
StudentId,
ProgramCode,
Program,
EffectiveTerm,
EffectiveTermTitle,
SequenceNumber,
AdmissionStatus,
AwardTypeCode,
AwardType,
PrimaryObjective,
ProgramStatusCode,
GraduationStatusCode,
ExpectedGraduationTerm,
ExpectedGraduationTermTitle,
ActualGraduationTerm,
ActualGraduationTermTitle,
GraduationAge,
GraduationAgeRange,
GraduationHonorsCode,
TransferMajorCode,
TransferMajor,
TransferInstitutionId,
TransferInstitution,
ReportedTerm,
ReportedTermTitle,
RowIsCurrent,
RowStartDate,
RowEndDate
FROM [dbo].[Staged_dbo_DimStudentObjective__New]
|
"
/* SQL INSERT into DimStudentObjective from New Temp Table */
INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudentObjective]
(
StudentObjectiveAK,
StudentId,
ProgramCode,
Program,
EffectiveTerm,
EffectiveTermTitle,
SequenceNumber,
AdmissionStatus,
AwardTypeCode,
AwardType,
PrimaryObjective,
ProgramStatusCode,
GraduationStatusCode,
ExpectedGraduationTerm,
ExpectedGraduationTermTitle,
ActualGraduationTerm,
ActualGraduationTermTitle,
GraduationAge,
GraduationAgeRange,
GraduationHonorsCode,
TransferMajorCode,
TransferMajor,
TransferInstitutionId,
TransferInstitution,
ReportedTerm,
ReportedTermTitle,
RowIsCurrent,
RowStartDate,
RowEndDate
)
SELECT
StudentObjectiveAK,
StudentId,
ProgramCode,
Program,
EffectiveTerm,
EffectiveTermTitle,
SequenceNumber,
AdmissionStatus,
AwardTypeCode,
AwardType,
PrimaryObjective,
ProgramStatusCode,
Gra |
This variable contains the string that will be used to insert any new rows into the DimStudentTerm table. |
|
DWOperations_Maintenance |
Configuration setting in SSIS_Configurations that specifies the name of the Operations Database. |
||
/*
Sample Update Type1 to Dim
*/
UPDATE [EnterpriseDW].[dbo].[DimStudentObjective]
SET
StudentObjectiveAK = SCD1.StudentObjectiveAK,
StudentId = SCD1.StudentId,
ProgramCode = SCD1.ProgramCode,
Program = SCD1.Program,
EffectiveTerm = SCD1.EffectiveTerm,
EffectiveTermTitle = SCD1.EffectiveTermTitle,
SequenceNumber = SCD1.SequenceNumber,
AdmissionStatus = SCD1.AdmissionStatus,
AwardTypeCode = SCD1.AwardTypeCode,
AwardType = SCD1.AwardType,
PrimaryObjective = SCD1.PrimaryObjective,
ProgramStatusCode = SCD1.ProgramStatusCode,
GraduationStatusCode = SCD1.GraduationStatusCode,
ExpectedGraduationTerm = SCD1.ExpectedGraduationTerm,
ExpectedGraduationTermTitle = SCD1.ExpectedGraduationTermTitle,
ActualGraduationTerm = SCD1.ActualGraduationTerm,
ActualGraduationTermTitle = SCD1.ActualGraduationTermTitle,
GraduationAge = SCD1.GraduationAge,
GraduationAgeRange = SCD1.GraduationAgeRange,
GraduationHonorsCode = SCD1.GraduationHonorsCode,
TransferMajorCode = SCD1.TransferMajorCode,
TransferMajor = SCD1.TransferMajor,
TransferInstitutionId = SCD1.TransferInstitutionId,
TransferInstitution = SCD1.TransferInstitution,
ReportedTerm = SCD1.ReportedTerm,
ReportedTermTitle = SCD1.ReportedTermTitle
FROM [EnterpriseDW].[dbo].[DimStudentObjective] AS [DIM]
INNER JOIN [dbo].[Staged_dbo_DimStudentObjective__Type1Update] AS SCD1
ON Dim.StudentObjectiveAK = SCD1.StudentObjectiveAK
|
"
/*
Sample Update Type1 to Dim
*/
UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudentObjective]
SET
StudentObjectiveAK = SCD1.StudentObjectiveAK,
StudentId = SCD1.StudentId,
ProgramCode = SCD1.ProgramCode,
Program = SCD1.Program,
EffectiveTerm = SCD1.EffectiveTerm,
EffectiveTermTitle = SCD1.EffectiveTermTitle,
SequenceNumber = SCD1.SequenceNumber,
AdmissionStatus = SCD1.AdmissionStatus,
AwardTypeCode = SCD1.AwardTypeCode,
AwardType = SCD1.AwardType,
PrimaryObjective = SCD1.PrimaryObjective,
ProgramStatusCode = SCD1.ProgramStatusCode,
GraduationStatusCode = SCD1.GraduationStatusCode,
ExpectedGraduationTerm = SCD1.ExpectedGraduationTerm,
ExpectedGraduationTermTitle = SCD1.ExpectedGraduationTermTitle,
ActualGraduationTerm = SCD1.ActualGraduationTerm,
ActualGraduationTermTitle = SCD1.ActualGraduationTermTitle,
GraduationAge = SCD1.GraduationAge,
GraduationAgeRange = SCD1.Graduati |
The SQL string that will update any changed rows in the DimStudentTerm dimension. |
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
