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].[DimFeeType] WHERE FeeTypeSK IN ( SELECT FeeTypeSK FROM [dbo].[Obsolete_DimFeeType] obs WHERE obs.DoNotDeleteFlag = 0 )

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

The SQL Command to delete Dimension records that were removed from the source system.

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.

/* SQL INSERT into DimFeeType from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimFeeType] (FeeTypeAK ,FeeTypeCode ,FeeType ,FeeCategoryCode ,FeeCategory ,FeeGroupCode ,FeeGroup ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT FeeTypeAK ,FeeTypeCode ,FeeType ,FeeCategoryCode ,FeeCategory ,FeeGroupCode ,FeeGroup ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimFeeType__New

" /* SQL INSERT into DimFeeType from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimFeeType] (FeeTypeAK ,FeeTypeCode ,FeeType ,FeeCategoryCode ,FeeCategory ,FeeGroupCode ,FeeGroup ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT FeeTypeAK ,FeeTypeCode ,FeeType ,FeeCategoryCode ,FeeCategory ,FeeGroupCode ,FeeGroup ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimFeeType__New "

This variable contains the string that will be used to insert any new rows into the DimFeeType 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].[DimFeeType] SET FeeTypeAK = SCD1.FeeTypeAK ,FeeTypeCode = SCD1.FeeTypeCode ,FeeType = SCD1.FeeType ,FeeCategoryCode = SCD1.FeeCategoryCode ,FeeCategory = SCD1.FeeCategory ,FeeGroupCode = SCD1.FeeGroupCode ,FeeGroup = SCD1.FeeGroup FROM [EnterpriseDW].[dbo].[DimFeeType] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimFeeType__Type1Update] AS SCD1 ON Dim.FeeTypeAK = SCD1.FeeTypeAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimFeeType] SET FeeTypeAK = SCD1.FeeTypeAK ,FeeTypeCode = SCD1.FeeTypeCode ,FeeType = SCD1.FeeType ,FeeCategoryCode = SCD1.FeeCategoryCode ,FeeCategory = SCD1.FeeCategory ,FeeGroupCode = SCD1.FeeGroupCode ,FeeGroup = SCD1.FeeGroup FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimFeeType] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimFeeType__Type1Update] AS SCD1 ON Dim.FeeTypeAK = SCD1.FeeTypeAK "

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 DimFeeType table to ensure that all records were copied.