Data Flow Task

Data Flow Task

Data Flow Task

Data Flow Task

Sequence

Sequence Container

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

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.

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.