|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | FactHoursWorkedByWeekLoad |
| Description | This package will extract the number of hours reported per Week depending on whether the source data is reported on Daily or Weekly. One row per Employee, Position, Assignment per week per Normal and Overtime Type will be extracted. |
| 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 | 07c96d8f-d0fc-4bda-b652-ac95aafe89f5 |
| 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 | 384 |
| Version Comments | |
| Version Guid | d3de4751-ec62-40c3-97ef-26ee24248963 |
| Version Major | 1 |
| Version Minor | 0 |
Executables
| Name | Type | Description |
Data Flow Task |
Data Flow Task |
|
Sequence |
Sequence Container |
|
Sequence |
Sequence Container |
|
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. |
||
IF 2=3
BEGIN
SELECT
CAST(0 AS INT) AS EmployeeAK
,CAST('aaa' AS VARCHAR(8)) AS PositionAK
,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
,CAST(0 AS INT) AS StartDateSK
,CAST(0 AS INT) AS EndDateSK
,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK
-- Calculate the average for the OverTime hours over the number of days in the Reporting Period
,CAST(0 AS NUMERIC(9,4)) AS HoursWorked
END
DECLARE @OverTimeType VARCHAR(4)
-- Find out what TimeType to use for OverTime Hours
SET @OverTimeType =
(
SELECT CAST(DESCRIPTION AS VARCHAR(4)) AS TimeTypeAK
FROM UTL_CODE_TABLE
WHERE TABLE_NAME = 'DW-PARMS'
AND CODE = 'HR-OVERTM'
AND STATUS = 'A'
)
-- Get the Normal and OverTime hours if we are reporting per Day from Attendance-Day
SELECT
AttendDay.PERSON_ID_TD AS EmployeeAK
,POSITION_CODE_TD AS PositionAK
,CAST(
REPLICATE('0', 9 - LEN(AttendDay.PERSON_ID_TD)) + CAST(AttendDay.PERSON_ID_TD AS VARCHAR(9))
+
CAST(AttendDay.POSITION_CODE_TD AS VARCHAR(8))
+ REPLICATE(' ', 8 - LEN(AttendDay.POSITION_CODE_TD)) +
REPLICATE('0', 3 - LEN(AttendDay.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendDay.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3))
AS VARCHAR(20)) AS AssignmentAK
,StartDate.SundayDate AS StartDateSK
,EndDate.SaturdayDate AS EndDateSK
,CAST(
CASE
WHEN TimeType.OVERTIME_IND = 1 THEN @OverTimeType
ELSE 'NORM'
END AS VARCHAR(4)) AS TimeTypeAK
,CAST(ISNULL(AttendDay.HOURS_USED,0) AS NUMERIC(9,4)) AS HoursWorked
FROM ATTENDANCE_AND_DAY_A AttendDay
-- Find the Sunday before the Reporting Day
OUTER APPLY
(
SELECT MAX(DateSK) AS SundayDate
FROM[EnterpriseDW ].[dbo].[DimDate] GetSun
WHERE GetSun.DayOfWeek = 1
AND GetSun.DateSK <= AttendDay.ATTENDANCE_DAY_DATE
) StartDate
-- Find the Saturday after the Reporting Day
OUTER APPLY
(
SELECT MIN(DateSK) AS SaturdayDate
FROM [EnterpriseDW ].[dbo].[DimDate] GetSat
WHERE GetSat.DayOfWeek = 7
AND GetSat.DateSK >= AttendDay.ATTENDANCE_DAY_DATE
) EndDate
LEFT OUTER JOIN TIME_TYPE_A TimeType
ON
(
TimeType.TIME_TYPE_CODE = AttendDay.TIME_TYPE_CODE
)
WHERE AttendDay.ATTENDANCE_DAY_INTERNAL_ID IS NOT NULL
AND (AttendDay.TIME_TYPE_CODE = 'NORM' OR TimeType.OVERTIME_IND = 1)
AND AttendDay.UNPROCESSED_IND = 0 |
"
IF 2=3
BEGIN
SELECT
CAST(0 AS INT) AS EmployeeAK
,CAST('aaa' AS VARCHAR(8)) AS PositionAK
,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
,CAST(0 AS INT) AS StartDateSK
,CAST(0 AS INT) AS EndDateSK
,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK
-- Calculate the average for the OverTime hours over the number of days in the Reporting Period
,CAST(0 AS NUMERIC(9,4)) AS HoursWorked
END
DECLARE @OverTimeType VARCHAR(4)
-- Find out what TimeType to use for OverTime Hours
SET @OverTimeType =
(
SELECT CAST(DESCRIPTION AS VARCHAR(4)) AS TimeTypeAK
FROM UTL_CODE_TABLE
WHERE TABLE_NAME = 'DW-PARMS'
AND CODE = 'HR-OVERTM'
AND STATUS = 'A'
)
-- Get the Normal and OverTime hours if we are reporting per Day from Attendance-Day
SELECT
AttendDay.PERSON_ID_TD AS EmployeeAK
,POSITION_CODE_TD AS PositionAK
,CAST(
REPLICATE('0', 9 - LEN(AttendDay.PERSON_ID_TD)) + CAST(AttendDay.PERSON_ID_TD AS VARCHAR(9))
+
|
||
IF 2=3
BEGIN
SELECT
CAST(0 AS INT) AS EmployeeAK
,CAST('aaa' AS VARCHAR(8)) AS PositionAK
,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
,CAST(0 AS INT) AS StartDateSK
,CAST(0 AS INT) AS EndDateSK
,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK
-- Calculate the average for the OverTime hours over the number of days in the Reporting Period
,CAST(0 AS NUMERIC(9,4)) AS HoursWorked
END
SELECT
AttendWeek.PERSON_ID AS EmployeeAK
,POSITION_CODE AS PositionAK
,CAST(
REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9))
+
CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendWeek.POSITION_CODE))
+
REPLICATE('0', 3 - LEN(AttendWeek.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendWeek.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3))
AS VARCHAR(20)) AS AssignmentAK
,StartDate.SundayDate AS StartDateSK
,EndDate.SaturdayDate AS EndDateSK
,CAST('NORM' AS VARCHAR(4)) AS TimeTypeAK
-- Calculate the average for the Normal hours over the number of days in the Reporting Period
,CAST(ROUND(ISNULL(AttendWeek.TOTAL_NORMAL_HOURS, 0) / (DATEDIFF(DD, CAST(AttendWeek.ATTENDANCE_START_DATE AS DATE), CAST(AttendWeek.ATTENDANCE_END_DATE AS DATE)) + 1),4) AS NUMERIC(9,4)) AS HoursWorked
FROM ATTENDANCE_AND_DAY_A AttendWeek
-- Create one record for each day in the Reporting Period
LEFT OUTER JOIN [EnterpriseDW ].[dbo].[DimDate] Daily
ON
(
Daily.DateSK >= AttendWeek.ATTENDANCE_START_DATE
AND Daily.DateSK <= AttendWeek.ATTENDANCE_END_DATE
)
-- Get the Sunday before each day in the Period
OUTER APPLY
(
SELECT MAX(DateSK) AS SundayDate
FROM [EnterpriseDW].[dbo].[DimDate] GetSun
WHERE GetSun.DayOfWeek = 1
AND GetSun.DateSK <= Daily.DateSK
) StartDate
-- Get the Saturday after each day in the Period
OUTER APPLY
(
SELECT MIN(DateSK) AS SaturdayDate
FROM [EnterpriseDW].[dbo].[DimDate] GetSat
WHERE GetSat.DayOfWeek = 7
AND GetSat.DateSK >= Daily.DateSK
) EndDate
WHERE AttendWeek.ATTENDANCE_INTERNAL_ID IS NOT NULL
AND AttendWeek.TOTAL_NORMAL_HOURS IS NOT NULL
AND AttendWeek.UNPROCESSED_IND = 0
|
"IF 2=3
BEGIN
SELECT
CAST(0 AS INT) AS EmployeeAK
,CAST('aaa' AS VARCHAR(8)) AS PositionAK
,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
,CAST(0 AS INT) AS StartDateSK
,CAST(0 AS INT) AS EndDateSK
,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK
-- Calculate the average for the OverTime hours over the number of days in the Reporting Period
,CAST(0 AS NUMERIC(9,4)) AS HoursWorked
END
SELECT
AttendWeek.PERSON_ID AS EmployeeAK
,POSITION_CODE AS PositionAK
,CAST(
REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9))
+
CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendWeek.POSITION_CODE))
+
REPLICATE('0', 3 - LEN(AttendWeek.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendWeek.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3))
AS VARCHAR(20)) AS AssignmentAK
,StartDate.SundayDate AS StartDateSK
,EndDate.SaturdayDate AS EndDa |
This is the SQL statement to extract all the Student Fee Data from the Source System. |
|
IF 2=3
BEGIN
SELECT
CAST(0 AS INT) AS EmployeeAK
,CAST('aaa' AS VARCHAR(8)) AS PositionAK
,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
,CAST(0 AS INT) AS StartDateSK
,CAST(0 AS INT) AS EndDateSK
,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK
-- Calculate the average for the OverTime hours over the number of days in the Reporting Period
,CAST(0 AS NUMERIC(9,4)) AS HoursWorked
END
DECLARE @OverTimeType VARCHAR(4)
-- Find out what TimeType to use for OverTime Hours
SET @OverTimeType =
(
SELECT CAST(ISNULL(DESCRIPTION,'N/A') AS VARCHAR(4)) AS TimeTypeAK
FROM UTL_CODE_TABLE
WHERE TABLE_NAME = 'DW-PARMS'
AND CODE = 'HR-OVERTM'
AND STATUS = 'A'
)
SELECT
AttendWeek.PERSON_ID AS EmployeeAK
,POSITION_CODE AS PositionAK
,CAST(
REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9))
+
CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendWeek.POSITION_CODE))
+
REPLICATE('0', 3 - LEN(AttendWeek.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendWeek.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3))
AS VARCHAR(20)) AS AssignmentAK
,StartDate.SundayDate AS StartDateSK
,EndDate.SaturdayDate AS EndDateSK
,@OverTimeType AS TimeTypeAK
-- Calculate the average for the OverTime hours over the number of days in the Reporting Period
,CAST(ROUND(ISNULL(AttendWeek.TOTAL_OVERTIME_HOURS, 0) / (DATEDIFF(DD, CAST(AttendWeek.ATTENDANCE_START_DATE AS DATE), CAST(AttendWeek.ATTENDANCE_END_DATE AS DATE)) + 1),4) AS NUMERIC(9,4)) AS HoursWorked
FROM ATTENDANCE_AND_DAY_A AttendWeek
LEFT OUTER JOIN [EnterpriseDW].[dbo].[DimDate] Daily
ON
(
Daily.DateSK >= AttendWeek.ATTENDANCE_START_DATE
AND Daily.DateSK <= AttendWeek.ATTENDANCE_END_DATE
)
OUTER APPLY
(
SELECT MAX(DateSK) AS SundayDate
FROM [EnterpriseDW].[dbo].[DimDate] GetSun
WHERE GetSun.DayOfWeek = 1
AND GetSun.DateSK <= Daily.DateSK
) StartDate
OUTER APPLY
(
SELECT MIN(DateSK) AS SaturdayDate
FROM [EnterpriseDW].[dbo].[DimDate] GetSat
WHERE GetSat.DayOfWeek = 7
AND GetSat.DateSK >= Daily.DateSK
) EndDate
WHERE AttendWeek.ATTENDANCE_INTERNAL_ID IS NOT NULL
AND AttendWeek.TOTAL_OVERTIME_HOURS IS NOT NULL
AND AttendWeek.UNPROCESSED_IND = 0
|
"
IF 2=3
BEGIN
SELECT
CAST(0 AS INT) AS EmployeeAK
,CAST('aaa' AS VARCHAR(8)) AS PositionAK
,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
,CAST(0 AS INT) AS StartDateSK
,CAST(0 AS INT) AS EndDateSK
,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK
-- Calculate the average for the OverTime hours over the number of days in the Reporting Period
,CAST(0 AS NUMERIC(9,4)) AS HoursWorked
END
DECLARE @OverTimeType VARCHAR(4)
-- Find out what TimeType to use for OverTime Hours
SET @OverTimeType =
(
SELECT CAST(ISNULL(DESCRIPTION,'N/A') AS VARCHAR(4)) AS TimeTypeAK
FROM UTL_CODE_TABLE
WHERE TABLE_NAME = 'DW-PARMS'
AND CODE = 'HR-OVERTM'
AND STATUS = 'A'
)
SELECT
AttendWeek.PERSON_ID AS EmployeeAK
,POSITION_CODE AS PositionAK
,CAST(
REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9))
+
CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendW |
This is the SQL statement to extract all the Archived Student Fee 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 |
