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].[DimCompensationType] WHERE CompensationTypeSK IN ( SELECT CompensationTypeSK FROM [dbo].[Obsolete_DimCompensationType] obs WHERE obs.DoNotDeleteFlag = 0 )

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

This is the SQL statement that will delete obsolete records from the DimGlTransDetail table.

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 DimCompensationType from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimCompensationType] (CompensationTypeAK ,CompensationCategory ,CompensationTypeCode ,CompensationType ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT CompensationTypeAK ,CompensationCategory ,CompensationTypeCode ,CompensationType ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimCompensationType__New

" /* SQL INSERT into DimCompensationType from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimCompensationType] (CompensationTypeAK ,CompensationCategory ,CompensationTypeCode ,CompensationType ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT CompensationTypeAK ,CompensationCategory ,CompensationTypeCode ,CompensationType ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimCompensationType__New "

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

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].[DimCompensationType] SET CompensationTypeAK = SCD1.CompensationTypeAK ,CompensationCategory = SCD1.CompensationCategory ,CompensationTypeCode = SCD1.CompensationTypeCode ,CompensationType = SCD1.CompensationType FROM [EnterpriseDW].[dbo].[DimCompensationType] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimCompensationType__Type1Update] AS SCD1 ON Dim.CompensationTypeAK = SCD1.CompensationTypeAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimCompensationType] SET CompensationTypeAK = SCD1.CompensationTypeAK ,CompensationCategory = SCD1.CompensationCategory ,CompensationTypeCode = SCD1.CompensationTypeCode ,CompensationType = SCD1.CompensationType FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimCompensationType] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimCompensationType__Type1Update] AS SCD1 ON Dim.CompensationTypeAK = SCD1.CompensationTypeAK "

The SQL string that will update any changed rows in the DimGlTransDetail 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.