Data Flow Task

Data Flow Task

Data Flow Task

Data Flow Task

Sequence

Sequence Container

Sequence

Sequence Container

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Y

Configuration setting in SSIS_Configurations that determines if Alerts will be send via e-mail in case of an abend or data problems

DELETE FROM [EnterpriseDW] .[dbo].[DimPayable] WHERE PayableSK IN ( SELECT PayableSK FROM [dbo].[Obsolete_DimPayable] obs WHERE obs.DoNotDeleteFlag = 0 )

" DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "] .[dbo].[DimPayable] WHERE PayableSK IN ( SELECT PayableSK FROM [dbo].[Obsolete_DimPayable] obs WHERE obs.DoNotDeleteFlag = 0 ) "

Configuration setting from SSIS_Configuration to specify the domain where the e-mail server is running.

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.

The list of files to be attached to the e-mail detailing record counts and data validation issues.

Testing (text maintained in SSIS Configurations table)

The text that will be coded in the body of the e-mail. This is an configuration setting in SSIS_Configurations that will be appended to the end of the e-mail.

C:\DW Local Stuff\SSIS\

The directory on the File System where the files to be attached to the e-mail for notifications will be stored. This is a configuration setting from SSIS_Configurations.

help@hetgroup.org

Configuration setting from SSIS_Configurations that will specify the From E-mail address to be used in e-mail notifications.

datawarehouse

The configuration setting from SSIS_Configurations that specify the password used for the e-mail server when sending e-mail notifications.

ipeterso@hetgroup.org

The configuration setting from SSIS_Configurations that specify the To Email address when sending e-mail notifications. This cannot be multiple e-mail addresses, but can be a distribution list.

windows

The configuration setting from SSIS_Configurations that specify the userid used for the e-mail server when sending e-mail notifications.

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(Payable.FISCAL_YEAR + Payable.PAYABLE_NBR as varchar(12)) as PayableAK ,Payable.FISCAL_YEAR as FiscalYear ,Payable.PAYABLE_NBR as PayableNumber ,ISNULL(Payable.ORIGIN, 'N/A') as OriginCode ,ISNULL(OrigDesc.DESCRIPTION, 'N/A') as Origin ,ISNULL(Payable.REF_TYPE, 'N/A') as ReferenceTypeCode ,CASE WHEN Payable.ORIGIN = 'DSBR' THEN ISNULL(DsbrDesc.DESCRIPTION,'N/A') WHEN Payable.ORIGIN = 'PUR' THEN ISNULL(RefTypesDesc.DESCRIPTION,'N/A') WHEN Payable.ORIGIN = 'MEMO' THEN ISNULL(CrTypesDesc.DESCRIPTION,'N/A') ELSE ISNULL(RefTypesMiscDesc.DESCRIPTION,'N/A') END AS ReferenceType ,CAST(CASE WHEN REF_NBR IS NULL THEN 'N/A' ELSE LEFT(REF_NBR,4) + '-' + SUBSTRING(REF_NBR,5,15) END AS VARCHAR(20)) AS ReferenceNumber ,ISNULL(Payment.PAYMENT_TYPE, 'N/A') as PaymentTypeCode ,CASE WHEN Payment.PAYMENT_TYPE = 'CHK' THEN 'Check' WHEN Payment.PAYMENT_TYPE = 'EFT' THEN 'Electronic Fund Transfer' WHEN Payment.PAYMENT_TYPE = 'CCR' THEN 'Refund to Credit Card' ELSE 'N/A' END AS PaymentType ,ISNULL(Payment.CHECK_EFT_CCR_NBR,'N/A') AS PaymentTypeNumber ,CAST(ISNULL(Payable.PAY_STATUS, 'N/A') AS VARCHAR(3)) AS PayableStatusCode ,CAST(ISNULL(StatusDesc.STATUS_DESC, 'N/A') AS VARCHAR(30)) as PayableStatus ,CAST( CASE WHEN Payable.PAY_DATE IS NULL THEN '1900-01-01' WHEN Payable.PAY_DATE = ' ' THEN '1900-01-01' WHEN ISDATE(Payable.PAY_DATE) = 1 THEN Payable.PAY_DATE ELSE '1900-01-01' END as datetime) AS PayableDate ,CAST(CASE WHEN Payable.PAYMENT_DATE IS NULL THEN '2098-01-01' WHEN Payable.PAYMENT_DATE = ' ' THEN '2098-01-01' WHEN ISDATE(Payable.PAYMENT_DATE) = 1 THEN Payable.PAYMENT_DATE ELSE '2098-12-31' END as DateTime) AS PaymentDate ,ISNULL(Payable.PAYMENT_XREF, 'N/A') as PaymentXrefNbr FROM AP_PAYABLE Payable LEFT OUTER JOIN AP_PAYMENT Payment ON ( LEFT(Payable.PAYMENT_XREF,4) = Payment.FISCAL_YEAR AND RIGHT(Payable.PAYMENT_XREF,8) = Payment.PAYMENT_NBR ) LEFT OUTER JOIN UTL_CODE_TABLE OrigDesc ON ( OrigDesc.TABLE_NAME = 'ORIGINS' AND OrigDesc.CODE = Payable.ORIGIN AND OrigDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE DsbrDesc ON ( DsbrDesc.TABLE_NAME = 'DISBTYPES' AND DsbrDesc.CODE = Payable.REF_TYPE AND DsbrDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE RefTypesDesc ON ( RefTypesDesc.TABLE_NAME = 'REF-TYPES' AND RefTypesDesc.CODE = Payable.REF_TYPE AND RefTypesDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE CrTypesDesc ON ( CrTypesDesc.TABLE_NAME = 'CR-REASONS' AND CrTypesDesc.CODE = Payable.REF_TYPE AND CrTypesDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE RefTypesMiscDesc ON ( RefTypesMiscDesc.TABLE_NAME = 'REFTY-MISC' AND RefTypesMiscDesc.CODE = Payable.REF_TYPE AND RefTypesMiscDesc.STATUS = 'A' ) LEFT OUTER JOIN FIP_TABLES StatusDesc ON ( StatusDesc.STATUS_CODE = Payable.PAY_STATUS ) WHERE (CAST(Payable.FISCAL_YEAR + Payable.PAYABLE_NBR as varchar(12))) IN ( /* Select changed records from Student File */ SELECT CAST(FISCAL_YEAR + PAYABLE_NBR as varchar(12)) FROM AP_PAYABLE WHERE ( AP_PAYABLE.RowUpdatedOn >= @IncrementalBeginDtTm AND AP_PAYABLE.RowUpdatedOn <= @IncrementalEndDtTm ) )

"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'SELECT CAST(Payable.FISCAL_YEAR + Payable.PAYABLE_NBR as varchar(12)) as PayableAK ,Payable.FISCAL_YEAR as FiscalYear ,Payable.PAYABLE_NBR as PayableNumber ,ISNULL(Payable.ORIGIN, 'N/A') as OriginCode ,ISNULL(OrigDesc.DESCRIPTION, 'N/A') as Origin ,ISNULL(Payable.REF_TYPE, 'N/A') as ReferenceTypeCode ,CASE WHEN Payable.ORIGIN = 'DSBR' THEN ISNULL(DsbrDesc.DESCRIPTION,'N/A') WHEN Payable.ORIGIN = 'PUR' THEN ISNULL(RefTypesDesc.DESCRIPTION,'N/A') WHEN Payable.ORIGIN = 'MEMO' THEN ISNULL(CrTypesDesc.DESCRIPTION,'N/A') ELSE ISNULL(RefTypesMiscDesc.DESCRIPTION,'N/A') END AS ReferenceType ,CAST(CASE WHEN REF_NBR IS NULL THEN 'N/A' ELSE LEFT(REF_NBR,4) + '-' + SUBSTRING(REF_NBR

This variable contains the SQL Query that will retrieve the data from the AP_PAYABLE table to load into the DimPayable dimension

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 DimPayable from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimPayable] (PayableAK ,FiscalYear ,PayableNumber ,OriginCode ,Origin ,ReferenceTypeCode ,ReferenceType ,ReferenceNumber ,PaymentTypeCode ,PaymentType ,PaymentTypeNumber ,PayableStatusCode ,PayableStatus ,PayableDate ,PaymentDate ,PaymentXrefNbr ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT PayableAK ,FiscalYear ,PayableNumber ,OriginCode ,Origin ,ReferenceTypeCode ,ReferenceType ,ReferenceNumber ,PaymentTypeCode ,PaymentType ,PaymentTypeNumber ,PayableStatusCode ,PayableStatus ,PayableDate ,PaymentDate ,PaymentXrefNbr ,RowIsCurrent ,RowStartDate ,RowEndDate FROM dbo.Staged_dbo_DimPayable__New

" /* SQL INSERT into DimPayable from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimPayable] (PayableAK ,FiscalYear ,PayableNumber ,OriginCode ,Origin ,ReferenceTypeCode ,ReferenceType ,ReferenceNumber ,PaymentTypeCode ,PaymentType ,PaymentTypeNumber ,PayableStatusCode ,PayableStatus ,PayableDate ,PaymentDate ,PaymentXrefNbr ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT PayableAK ,FiscalYear ,PayableNumber ,OriginCode ,Origin ,ReferenceTypeCode ,ReferenceType ,ReferenceNumber ,PaymentTypeCode ,PaymentType ,PaymentTypeNumber ,PayableStatusCode ,PayableStatus ,PayableDate ,PaymentDate ,PaymentXrefNbr ,RowIsCurren

This variable contains the string that will be used to insert any new rows into the DimPayable table.

DWOperations_Maintenance

Configuration setting in SSIS_Configurations that specifies the name of the Operations Database.

0

The number of rows that were loaded into the Dimension. This is will be compared against the row count from the source system.

/* Sample Update Type1 to Dim */ UPDATE [EnterpriseDW].[dbo].[DimPayable] SET PayableAK = SCD1.PayableAK ,FiscalYear = SCD1.FiscalYear ,PayableNumber = SCD1.PayableNumber ,OriginCode = SCD1.OriginCode ,Origin = SCD1.Origin ,ReferenceTypeCode = SCD1.ReferenceTypeCode ,ReferenceType = SCD1.ReferenceType ,ReferenceNumber = SCD1.ReferenceNumber ,PaymentTypeCode = SCD1.PaymentTypeCode ,PaymentType = SCD1.PaymentType ,PaymentTypeNumber = SCD1.PaymentTypeNumber ,PayableStatusCode = SCD1.PayableStatusCode ,PayableStatus = SCD1.PayableStatus ,PayableDate = SCD1.PayableDate ,PaymentDate = SCD1.PaymentDate ,PaymentXrefNbr = SCD1.PaymentXrefNbr FROM [EnterpriseDW].[dbo].[DimPayable] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimPayable__Type1Update] AS SCD1 ON Dim.PayableAK = SCD1.PayableAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimPayable] SET PayableAK = SCD1.PayableAK ,FiscalYear = SCD1.FiscalYear ,PayableNumber = SCD1.PayableNumber ,OriginCode = SCD1.OriginCode ,Origin = SCD1.Origin ,ReferenceTypeCode = SCD1.ReferenceTypeCode ,ReferenceType = SCD1.ReferenceType ,ReferenceNumber = SCD1.ReferenceNumber ,PaymentTypeCode = SCD1.PaymentTypeCode ,PaymentType = SCD1.PaymentType ,PaymentTypeNumber = SCD1.PaymentTypeNumber ,PayableStatusCode = SCD1.PayableStatusCode ,PayableStatus = SCD1.PayableStatus ,PayableDate = SCD1.PayableDate ,PaymentDate = SCD1.PaymentDate ,PaymentXrefNbr = SCD1.PaymentXrefNbr FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimPayable] AS [DIM] INNER JOIN [dbo].[Staged_dbo

The SQL string that will update any changed rows in the DimPayable dimension.

c:\stubfile.txt

Temporary file name that will be used to create the e-mail notifications. This is a configuration setting from SSIS_Configurations.

25

The SMTPPort for the e-mail server to be used when sending notification e-mails. This is a configuration setting from SSIS_Configurations.

www.ad.hetgroup.org

The SMTP Server Name contains the name for the e-mail server. This is a configuration setting from SSIS_Configurations.

0

This is the Row count from the Source System to compare against that of the DimCurriculum table to ensure that all records were copied.