Y

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

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 Staged DimApplication table */ INSERT INTO [EnterpriseDW].[dbo].[DimApplication] ([ApplicationAK] ,[StudentId] ,[ApplicationTerm] ,[ApplicationTermTitle] ,[ApplicationEntry] ,[ApplicationTypeCode] ,[ApplicationType] ,[CreditVocationalBachelorApplication] ,[InitialOrReadmitApplication] ,[BasisOfAdmissionCode] ,[BasisOfAdmission] ,[EnrollmentStatusCode] ,[EnrollmentStatus] ,[FirstTimeInCollege] ,[BachelorApplicant] ,[ProgramCode] ,[Program] ,[AwardTypeCode] ,[AwardType] ,[ApplicationDate] ,[FirstTermEnrolled] ,[FirstTermEnrolledTitle] ,[HomeCampusCode] ,[HomeCampus] ,[RowIsCurrent] ,[RowStartDate] ,[RowEndDate]) SELECT ApplicationAK ,StudentId ,ApplicationTerm ,ApplicationTermTitle ,ApplicationEntry ,ApplicationTypeCode ,ApplicationType ,CreditVocationalBachelorApplication ,InitialOrReadmitApplication ,BasisOfAdmissionCode ,BasisOfAdmission ,EnrollmentStatusCode ,EnrollmentStatus ,FirstTimeInCollege ,BachelorApplicant ,ProgramCode ,Program ,AwardTypeCode ,AwardType ,ApplicationDate ,FirstTermEnrolled ,FirstTermEnrolledTitle ,HomeCampusCode ,HomeCampus ,RowIsCurrent ,RowStartDate ,RowEndDate FROM [dbo].[Staged_dbo_DimApplication__New]

" /* SQL Insert into Staged DimApplication table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimApplication] ([ApplicationAK] ,[StudentId] ,[ApplicationTerm] ,[ApplicationTermTitle] ,[ApplicationEntry] ,[ApplicationTypeCode] ,[ApplicationType] ,[CreditVocationalBachelorApplication] ,[InitialOrReadmitApplication] ,[BasisOfAdmissionCode] ,[BasisOfAdmission] ,[EnrollmentStatusCode] ,[EnrollmentStatus] ,[FirstTimeInCollege] ,[BachelorApplicant] ,[ProgramCode] ,[Program] ,[AwardTypeCode] ,[AwardType] ,[ApplicationDate] ,[FirstTermEnrolled] ,[FirstTermEnrolledTitle] ,[HomeCampusCode] ,[HomeCampus] ,[RowIsCurrent] ,[RowStartDate] ,[RowEndDate]) SELECT ApplicationAK ,StudentId ,ApplicationTerm ,ApplicationTermTitle ,Applic

The SQL statement that will insert the data into the DimStudent 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].[DimApplication] SET ApplicationAK = SCD1.ApplicationAK ,StudentId = SCD1.StudentId ,ApplicationTerm = SCD1.ApplicationTerm ,ApplicationTermTitle = SCD1.ApplicationTermTitle ,ApplicationEntry = SCD1.ApplicationEntry ,ApplicationTypeCode = SCD1.ApplicationTypeCode ,ApplicationType = SCD1.ApplicationType ,CreditVocationalBachelorApplication = SCD1.CreditVocationalBachelorApplication ,InitialOrReadmitApplication = SCD1.InitialOrReadmitApplication ,BasisOfAdmissionCode = SCD1.BasisOfAdmissionCode ,BasisOfAdmission = SCD1.BasisOfAdmission ,EnrollmentStatusCode = SCD1.EnrollmentStatusCode ,EnrollmentStatus = SCD1.EnrollmentStatus ,FirstTimeInCollege = SCD1.FirstTimeInCollege ,BachelorApplicant = SCD1.BachelorApplicant ,ProgramCode = SCD1.ProgramCode ,Program = SCD1.Program ,AwardTypeCode = SCD1.AwardTypeCode ,AwardType = SCD1.AwardType ,ApplicationDate = SCD1.ApplicationDate ,FirstTermEnrolled = SCD1.FirstTermEnrolled ,FirstTermEnrolledTitle = SCD1.FirstTermEnrolledTitle ,HomeCampusCode = SCD1.HomeCampusCode ,HomeCampus = SCD1.HomeCampus FROM [EnterpriseDW].[dbo].[DimApplication] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimApplication__Type1Update] AS SCD1 ON Dim.ApplicationAK = SCD1.ApplicationAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimApplication] SET ApplicationAK = SCD1.ApplicationAK ,StudentId = SCD1.StudentId ,ApplicationTerm = SCD1.ApplicationTerm ,ApplicationTermTitle = SCD1.ApplicationTermTitle ,ApplicationEntry = SCD1.ApplicationEntry ,ApplicationTypeCode = SCD1.ApplicationTypeCode ,ApplicationType = SCD1.ApplicationType ,CreditVocationalBachelorApplication = SCD1.CreditVocationalBachelorApplication ,InitialOrReadmitApplication = SCD1.InitialOrReadmitApplication ,BasisOfAdmissionCode = SCD1.BasisOfAdmissionCode ,BasisOfAdmission = SCD1.BasisOfAdmission ,EnrollmentStatusCode = SCD1.EnrollmentStatusCode ,EnrollmentStatus = SCD1.EnrollmentStatus ,FirstTimeInCollege = SCD1.FirstTimeInCollege ,BachelorApplic

The SQL Statement that will update the rows in the DimStudent Table.

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.