|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | FactStudentFeesPaidLoad |
| Description | This package will extract the Student Fee Payment Data. One row per Student Term Refence Number, Fee Type and Payment Source will be created. |
| 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 | b719dbb1-56ef-4f47-a531-4762968ae132 |
| 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 | EncryptSensitiveWithUserKey |
| Save Checkpoints | False |
| Suppress Configuration Warnings | False |
| Suspend Required | False |
| Transaction Option | Supported |
| Update Objects | False |
| Version Build | 342 |
| Version Comments | |
| Version Guid | 0c06d236-6683-4ed7-8ad3-5b0986775e1a |
| Version Major | 1 |
| Version Minor | 0 |
Executables
| Name | Type | Description |
Data Flow Task |
Data Flow Task |
|
Data Flow Task |
Data Flow Task |
|
Sequence |
Sequence Container |
|
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 |
DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM'SELECT
CAST(CASE
WHEN COUNT(*) > 0 Then 'Y'
ELSE 'N'
END AS char(1)) AS Result FROM UTL_CODE_TABLE UtlCode
WHERE (UtlCode.ISN_UTL_CODE_TABLE) IN
(
/* Select changed records from GL Trans File */
SELECT ISN_UTL_CODE_TABLE
FROM UTL_CODE_TABLE
WHERE (
UTL_CODE_TABLE.RowUpdatedOn >= @IncrementalBeginDtTm
AND UTL_CODE_TABLE.RowUpdatedOn <= @IncrementalEndDtTm
AND UTL_CODE_TABLE.TABLE_NAME = 'DWCUSTTYPE' )
)
|
"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'SELECT
CAST(CASE
WHEN COUNT(*) > 0 Then 'Y'
ELSE 'N'
END AS char(1)) AS Result FROM UTL_CODE_TABLE UtlCode
WHERE (UtlCode.ISN_UTL_CODE_TABLE) IN
(
/* Select changed records from GL Trans File */
SELECT ISN_UTL_CODE_TABLE
FROM UTL_CODE_TABLE
WHERE (
UTL_CODE_TABLE.RowUpdatedOn >= @IncrementalBeginDtTm
AND UTL_CODE_TABLE.RowUpdatedOn <= @IncrementalEndDtTm
AND UTL_CODE_TABLE.TABLE_NAME = 'DWCUSTTYPE' )
)
"
|
||
DELETE FROM [EnterpriseDW].[dbo].[FactStudentFeesPaid] WHERE StudentSK IN
(
SELECT OldStudentSK
FROM Obsolete_DimStudent
)
|
"DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[FactStudentFeesPaid] WHERE StudentSK IN
(
SELECT OldStudentSK
FROM Obsolete_DimStudent
)
"
|
This is the SQL statement that will be used to delete Fee information for Student ID that were deleted in the Merge Process. |
|
DELETE FROM [EnterpriseDW].[dbo].[FactStudentFeesPaid]
WHERE StudentTermSK
IN
(
SELECT FactPaid.StudentTermSK
FROM FactStudentFeesPaid_Staging StgeFact
INNER JOIN [EnterpriseDW].[dbo].[DimStudentTerm] StTerm
ON
(
StgeFact.StudentTermAK = StTerm.StudentTermAK
)
INNER JOIN [EnterpriseDW].[dbo].[FactStudentFeesPaid] FactPaid
ON
(
FactPaid.StudentTermSK = StTerm.StudentTermSK
)
)
|
"DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[FactStudentFeesPaid]
WHERE StudentTermSK
IN
(
SELECT FactPaid.StudentTermSK
FROM FactStudentFeesPaid_Staging StgeFact
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudentTerm] StTerm
ON
(
StgeFact.StudentTermAK = StTerm.StudentTermAK
)
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[FactStudentFeesPaid] FactPaid
ON
(
FactPaid.StudentTermSK = StTerm.StudentTermSK
)
)
"
|
This is the SQL Statement that will delete Fact records that will be reloaded. |
|
DELETE
FROM
[EnterpriseDW] .[dbo].[FactStudentFeesPaid]
WHERE StudentTermSK IN
(
SELECT StudentTermSK
FROM [dbo].[Obsolete_StudentFees] obs
)
|
"
DELETE
FROM
" + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "] .[dbo].[FactStudentFeesPaid]
WHERE StudentTermSK IN
(
SELECT StudentTermSK
FROM [dbo].[Obsolete_StudentFees] obs
)
" |
||
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_FactStudentFeesPaid_Select 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM' |
"exec usp_FactStudentFeesPaid_Select '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "'" |
This is the SQL statement to extract all the Student Fee Payment data from the Source System. |
|
exec usp_FactStudentFeesPaidArchived_Select 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM' |
"exec usp_FactStudentFeesPaidArchived_Select '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "'" |
This is the SQL statement to extract all the Archived Student Fee Payment 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. |
||
Y |
This SSIS_Configuration Setting will determine if a Full Load will be done or not. |
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
