Sequence

Sequence Container

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.

/* Remove Student Term Rows from DimStudentTerm for Old Students */ DELETE St FROM [EnterpriseDW].[dbo].[DimStudent] as St WHERE StudentId IN ( SELECT OldStudentIdAK FROM dbo.Obsolete_DimStudent as OldSt WHERE St.StudentId = OldSt.OldStudentIdAK )

" /* Remove Student Term Rows from DimStudentTerm for Old Students */ DELETE St FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudent] as St WHERE StudentId IN ( SELECT OldStudentIdAK FROM dbo.Obsolete_DimStudent as OldSt WHERE St.StudentId = OldSt.OldStudentIdAK ) "

This is the SQL statement to delete the Student records from the DimStudent Table if they exist on the Obsolete Student 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.

0

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

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