Data Flow Task

Data Flow 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.

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(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '07' + '01' AS int) AS DateSK ,SUBSTRING(Ledger.GL_ACCT_NO,1,16) AS OrgUnitAK ,SUBSTRING(Ledger.GL_ACCT_NO,17,9) AS GlCodeAK ,CAST(Ledger.FISCAL_YEAR + Ledger.GL_ACCT_NO AS varchar(29)) AS YearGlAccountNumberAK ,ISNULL(Monthly.AMOUNT, 0) AS Amount FROM GENERAL_LEDGER_FILE Ledger INNER JOIN GENERAL_LEDGER_FILE_MONTHLY_ACCUMS Monthly ON ( Ledger.ISN_GENERAL_LEDGER_FILE = Monthly.ISN_GENERAL_LEDGER_FILE AND Monthly.cnxarraycolumn = 0 AND Monthly.AMOUNT IS NOT NULL ) WHERE (Ledger.ISN_GENERAL_LEDGER_FILE) IN ( SELECT GENERAL_LEDGER_FILE.ISN_GENERAL_LEDGER_FILE FROM GENERAL_LEDGER_FILE WHERE ( GENERAL_LEDGER_FILE.RowUpdatedOn >= @IncrementalBeginDtTm AND GENERAL_LEDGER_FILE.RowUpdatedOn <= @IncrementalEndDtTm ) )

"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' SELECT CAST(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '07' + '01' AS int) AS DateSK ,SUBSTRING(Ledger.GL_ACCT_NO,1,16) AS OrgUnitAK ,SUBSTRING(Ledger.GL_ACCT_NO,17,9) AS GlCodeAK ,CAST(Ledger.FISCAL_YEAR + Ledger.GL_ACCT_NO AS varchar(29)) AS YearGlAccountNumberAK ,ISNULL(Monthly.AMOUNT, 0) AS Amount FROM GENERAL_LEDGER_FILE Ledger INNER JOIN GENERAL_LEDGER_FILE_MONTHLY_ACCUMS Monthly ON ( Ledger.ISN_GENERAL_LEDGER_FILE = Monthly.ISN_GENERAL_LEDGER_FILE AND Monthly.cnxarraycolumn = 0 AND Monthly.AMOUNT IS NOT NULL ) WHERE (Ledger.ISN_GENERAL_LEDGER_FILE) IN ( SELECT GENERAL_LEDGER_FILE.ISN_GENERAL_LEDGER_FILE FROM GENERAL_LEDGER_FILE WHERE ( GENERAL_LEDGER_FILE.RowUpdatedOn >= @IncrementalBegi

This is the SQL statement to retrieve the RollOverBalances from the GENERAL_LEDGER_FILE on 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.

/* Sample Update Type1 to Dim */ UPDATE [EnterpriseDW].[dbo].[FactGlRollOverBalances] SET DateSK = UPD1.DateSK ,OrgUnitSK = UPD1.OrgUnitSK ,GlCodeSK = UPD1.GlCodeSK ,GlAccountSK = UPD1.GlAccountSK ,Amount = UPD1.Amount FROM [EnterpriseDW].[dbo].[FactGlRollOverBalances] AS [FACT] INNER JOIN [dbo].[Staged_dbo_FactGlRollOverBalances__Update] AS UPD1 ON (FACT.GlAccountSK = UPD1.GlAccountSK AND FACT.DateSK = UPD1.DateSK AND FACT.GlCodeSK = UPD1.GlCodeSK AND FACT.OrgUnitSK = UPD1.OrgUnitSK)

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[FactGlRollOverBalances] SET DateSK = UPD1.DateSK ,OrgUnitSK = UPD1.OrgUnitSK ,GlCodeSK = UPD1.GlCodeSK ,GlAccountSK = UPD1.GlAccountSK ,Amount = UPD1.Amount FROM [EnterpriseDW].[dbo].[FactGlRollOverBalances] AS [FACT] INNER JOIN [dbo].[Staged_dbo_FactGlRollOverBalances__Update] AS UPD1 ON (FACT.GlAccountSK = UPD1.GlAccountSK AND FACT.DateSK = UPD1.DateSK AND FACT.GlCodeSK = UPD1.GlCodeSK AND FACT.OrgUnitSK = UPD1.OrgUnitSK) "