Data Flow Task

Data Flow Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

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.

UPDATE EnterpriseDW.dbo.FactStudentReceiptTotals SET StudentSK = obs_std.NewStudentSK FROM EnterpriseDW.dbo.FactStudentReceiptTotals 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);

" UPDATE " + @[User::EDWDBName] + ".dbo.FactStudentReceiptTotals SET StudentSK = obs_std.NewStudentSK FROM " + @[User::EDWDBName] + ".dbo.FactStudentReceiptTotals fct INNER JOIN " + @[User::EDWDBName] + ".dbo.DimStudent ds ON (fct.StudentSK = ds.StudentSK) INNER JOIN dbo.Obsolete_DimStudent obs_std ON (obs_std.OldStudentSK = ds.StudentSK); "

This is the SQL statement that will point any remaining Student Receipt Fact receords to the new Student ID after a Merge.

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 CASHIER_SESSION_KEY + RIGHT('00000' + CAST(RECEIPT_SEQ as varchar(5)),5) as StudentReceiptAK ,STUDENT_ID AS StudentIdAK ,CAST(CASE WHEN ISDATE(StReceipt.ADD_DATE) = 1 THEN StReceipt.ADD_DATE ELSE '1900-01-01' END AS DATETIME) AS DateAK ,CAST(RECEIPT_AMOUNT AS NUMERIC(11,2)) AS ReceiptTotal ,CAST(ISNULL(RECEIPT_AMOUNT,0) AS NUMERIC(11,2)) + CAST(ISNULL(RECEIPT_OTHER_1,0) AS NUMERIC(11,2)) AS ReceiptTotalPlusCoverage ,CAST(RECEIPT_OTHER_2 AS NUMERIC(11,2)) AS ReceiptFeesPaid ,CAST(TOTAL_RECV_PAID AS NUMERIC(11,2)) AS ReceiptReceivablesPaid ,CAST(TOTAL_OBLG_PAID AS NUMERIC(11,2)) AS ReceiptObligationsPaid FROM AR_RECEIPT_A StReceipt WHERE ( StReceipt.RowUpdatedOn >= @IncrementalBeginDtTm AND StReceipt.RowUpdatedOn <= @IncrementalEndDtTm AND StReceipt.STUDENT_ID IS NOT NULL AND StReceipt.RECEIPT_STATUS = 'CO' ) UNION ALL SELECT CASHIER_SESSION_KEY + RIGHT('00000' + CAST(RECEIPT_SEQ as varchar(5)),5) as StudentReceiptAK ,CUSTOMER_ID AS StudentIdAK ,CAST(CASE WHEN ISDATE(CustReceipt.ADD_DATE) = 1 THEN CustReceipt.ADD_DATE ELSE '1900-01-01' END AS DATETIME) AS DateAK ,CAST(RECEIPT_AMOUNT AS NUMERIC(11,2)) AS ReceiptTotal ,CAST(ISNULL(RECEIPT_AMOUNT,0) AS NUMERIC(11,2)) + CAST(ISNULL(RECEIPT_OTHER_1,0) AS NUMERIC(11,2)) AS ReceiptTotalPlusCoverage ,CAST(RECEIPT_OTHER_2 AS NUMERIC(11,2)) AS ReceiptFeesPaid ,CAST(TOTAL_RECV_PAID AS NUMERIC(11,2)) AS ReceiptReceivablesPaid ,CAST(TOTAL_OBLG_PAID AS NUMERIC(11,2)) AS ReceiptObligationsPaid FROM AR_RECEIPT_A CustReceipt INNER JOIN ST_STDNT_A Stdnt ON ( CustReceipt.CUSTOMER_ID = Stdnt.STUDENT_ID and Stdnt.STUDENT_ID IS NOT NULL ) WHERE ( CustReceipt.RowUpdatedOn >= @IncrementalBeginDtTm AND CustReceipt.RowUpdatedOn <= @IncrementalEndDtTm AND CustReceipt.STUDENT_ID IS NULL AND CustReceipt.CUSTOMER_ID IS NOT NULL AND CustReceipt.RECEIPT_STATUS = 'CO' )

"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' SELECT CASHIER_SESSION_KEY + RIGHT('00000' + CAST(RECEIPT_SEQ as varchar(5)),5) as StudentReceiptAK ,STUDENT_ID AS StudentIdAK ,CAST(CASE WHEN ISDATE(StReceipt.ADD_DATE) = 1 THEN StReceipt.ADD_DATE ELSE '1900-01-01' END AS DATETIME) AS DateAK ,CAST(RECEIPT_AMOUNT AS NUMERIC(11,2)) AS ReceiptTotal ,CAST(ISNULL(RECEIPT_AMOUNT,0) AS NUMERIC(11,2)) + CAST(ISNULL(RECEIPT_OTHER_1,0) AS NUMERIC(11,2)) AS ReceiptTotalPlusCoverage ,CAST(RECEIPT_OTHER_2 AS NUMERIC(11,2)) AS ReceiptFeesPaid ,CAST(TOTAL_RECV_PAID AS NUMERIC(11,2)) AS ReceiptReceivablesPaid ,CAST(TOTAL_OBLG_PAID AS NUMERIC(11,2)) AS ReceiptObligationsPaid FROM AR_RECEIPT_A StReceipt WHERE ( StReceipt.RowUpdatedOn >= @IncrementalBeginDtTm A

This is the SQL statement to extract all the Student Receipt Total information 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.