|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | FactEnrollmentSnapshotLoad |
| Description | This package loads data into the FactEnrollmentSnapshot table for the Current Date's Relative Day and the previous 6 Relative Days for all Active Terms. Additionally, the package will load the same 7 Relative Day period for the Current Year - 1 and Current Year - 2 years for the same Active Terms. The package will remove any data not within the days described above. |
| 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 | 51d97714-f96d-4336-8cfb-562552ce65e9 |
| 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 | 359 |
| Version Comments | |
| Version Guid | d113f8f9-79ba-47e3-8d40-63d2c917ac49 |
| Version Major | 1 |
| Version Minor | 0 |
Executables
| Name | Type | Description |
Data Flow Task |
Data Flow Task |
|
For Each Loop |
Foreach Loop Container |
|
For Each Loop |
Foreach Loop 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 |
|
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 |
2016-03-04 |
This is a configuration setting in SSIS_Configurations that is set to the date that the package is being executed on. |
||
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. |
||
System.Object |
|||
System.Object |
|||
System.Object |
This contains the Active Terms returned from a SQL select statement. |
||
System.Object |
This contains the Active Terms returned from a SQL select statement. |
||
Y |
This SSIS_Configuration Setting will determine if a Full Load will be done or not. |
||
7 |
|||
DELETE
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot
WHERE EnrollmentSnapshotSK NOT IN
(
SELECT fes.EnrollmentSnapshotSK
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot fes
INNER JOIN [EnterpriseDW].dbo.DimRelativeDay drd
ON (drd.RelativeDaySK = fes.RelativeDaySK)
INNER JOIN dbo.FactEnrollmentSnapshot_TermRelativeDays festrd
ON (
festrd.Term = drd.Term
AND
festrd.RelativeDayNumber = drd.RelativeDayNumber
)
)
|
"
DELETE
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot
WHERE EnrollmentSnapshotSK NOT IN
(
SELECT fes.EnrollmentSnapshotSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot fes
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimRelativeDay drd
ON (drd.RelativeDaySK = fes.RelativeDaySK)
INNER JOIN dbo.FactEnrollmentSnapshot_TermRelativeDays festrd
ON (
festrd.Term = drd.Term
AND
festrd.RelativeDayNumber = drd.RelativeDayNumber
)
)
"
|
||
UPDATE [EnterpriseDW].dbo.FactEnrollmentSnapshot
SET StudentSK = obs_std.NewStudentSK
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot 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.FactEnrollmentSnapshot
SET StudentSK = obs_std.NewStudentSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot 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.FactEnrollmentSnapshot
SET StudentTermSK = NEW_d_StdTrm.StudentTermSK
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot 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.FactEnrollmentSnapshot
SET StudentTermSK = NEW_d_StdTrm.StudentTermSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] +"].dbo.FactEnrollmentSnapshot 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_Std |
||
/*
Query provides list of Terms
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT DISTINCT
fct_enrll_ss_rd.Term
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM [EnterpriseDW].dbo.DimRelativeDay dim_rd
INNER JOIN [EnterpriseDW].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ss_rd.Term
AND
Fact_Term_DayNum.RelativeDayNumber = fct_enrll_ss_rd.RelativeDayNumber
)
WHERE Fact_Term_DayNum.Term IS NULL |
|||
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM [EnterpriseDW].dbo.DimRelativeDay dim_rd
INNER JOIN [EnterpriseDW].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ss_rd.Term
AND
Fact_Term_DayNum.RelativeDayNumber = fct_enrll_ss_rd.RelativeDayNumber
)
WHERE Fact_Term_DayNum.Term IS NULL AND fct_enrll_ss_rd.Term = '' |
"
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimRelativeDay dim_rd
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ |
||
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM [EnterpriseDW].dbo.DimRelativeDay dim_rd
INNER JOIN [EnterpriseDW].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ss_rd.Term
AND
Fact_Term_DayNum.RelativeDayNumber = fct_enrll_ss_rd.RelativeDayNumber
)
WHERE Fact_Term_DayNum.Term IS NULL AND fct_enrll_ss_rd.Term = '' |
"
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimRelativeDay dim_rd
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ |
||
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
