Task

Execute Package Task

Task

Execute Package Task

Task

Execute Package Task

Task

Execute Package Task

Task

Execute Package Task

Task

Execute Package Task

Task

Execute Package Task

ScriptTask

Sequence

Sequence Container

Sequence

Sequence Container

Sequence

Sequence Container

Sequence

Sequence Container

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

Task

Execute SQL Task

2016-03-04

This variable is set to the current date if the ETL process is executed after 6 am. If the ETL is executed prior to 6 am, it will be set to yesterday?s date. This variable will be used to extract the Enrollment information for that date. If the UseManualDates is set to Y, this variable will not be updated.

N

This variable will determine if the DimTerm will take place or not. If this variable is set to Y, the DimTerm dimension will not be realoaded.

Configuration setting from SSIS_Configuration to specify the domain where the e-mail server is running.

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.

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

Y

Jan 1 1900 12:00AM

The IncrementalBeginDtTm setting will contain the start date and time for which updates will be extracted. This is a configuration setting from SSIS_Configurations that are set in the Control Package.

Mar 4 2016 10:33AM

The IncrementalEndDtTm setting will contain the End Date and Time for which updates will be extracted. These values will be compared against the RowUpdatedOn column on the tables in the DWStaging database to determine which rows to extract and load into the Enterprise Data Warehouse.

Y

This SSIS_Configuration Setting will determine if a Full Load will be done or not.

0

C:\Data Warehouse\Project\SSIS\EnterpriseDW_SSIS\

The contains the folder where the SSIS packages are stored. This is used in the connection string in the Control package to execute all the SSIS packages.

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.

DECLARE @IsFullLoad varchar (1) = 'Y' DECLARE @UseManualDate varchar (1) = 'N' DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM' DECLARE @CurrentDayDate date = '2016-03-04' IF @IsFullLoad = 'Y' BEGIN SET @IncrementalBeginDtTm = '1900-01-01' SET @IncrementalEndDtTm = GETDATE() END ELSE IF @UseManualDate = 'N' BEGIN SET @IncrementalBeginDtTm = @IncrementalEndDtTm SET @IncrementalEndDtTm = GETDATE () END DECLARE @TmPortion TIME DECLARE @DaysToAdd INT = 0 DECLARE @IncrementalEndDt_DateOnly DATE SET @IncrementalEndDt_DateOnly = CAST(CONVERT(VARCHAR(10), @IncrementalEndDtTm, 101) AS date) SET @TmPortion = CAST(CONVERT(VARCHAR(20), @IncrementalEndDtTm, 108) AS time) -- If before 6am, use data up till the end of previous day. IF @TmPortion < '06:00' SET @DaysToAdd = -1 ELSE SET @DaysToAdd = 0 IF @UseManualDate = 'N' BEGIN SET @CurrentDayDate = dateadd(dd,@DaysToAdd,@IncrementalEndDt_DateOnly) END -- Update Config variables UPDATE [dbo].[SSIS Configurations] SET ConfiguredValue = @IncrementalBeginDtTm WHERE ConfigurationFilter = 'Var_IncrementalBeginDtTm' UPDATE [dbo].[SSIS Configurations] SET ConfiguredValue = @IncrementalEndDtTm WHERE ConfigurationFilter = 'Var_IncrementalEndDtTm' UPDATE [dbo].[SSIS Configurations] SET ConfiguredValue = @CurrentDayDate WHERE ConfigurationFilter = 'Var_CurrentDayDate'

" DECLARE @IsFullLoad varchar (1) = '" + @[User::IsFullLoad]+ "' DECLARE @UseManualDate varchar (1) = '" + @[User::UseManualDates] + "' DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' DECLARE @CurrentDayDate date = '" + @[User::CurrentDayDate] + "' IF @IsFullLoad = 'Y' BEGIN SET @IncrementalBeginDtTm = '1900-01-01' SET @IncrementalEndDtTm = GETDATE() END ELSE IF @UseManualDate = 'N' BEGIN SET @IncrementalBeginDtTm = @IncrementalEndDtTm SET @IncrementalEndDtTm = GETDATE () END DECLARE @TmPortion TIME DECLARE @DaysToAdd INT = 0 DECLARE @IncrementalEndDt_DateOnly DATE SET @IncrementalEndDt_DateOnly = CAST(CONVERT(VARCHAR(10), @IncrementalEndDtTm, 101) AS date) SET @TmPortion = CAST(CONVERT(VARCHAR(20), @IncrementalEndDtTm, 108) AS time) -- If before 6am, use data up till the end of previous day. IF @TmPortion < '06:00' SET @DaysToAdd = -1 ELSE SET @DaysToAdd

This is the variable that contains the SQL statement that is used to update the Incremental Begin and End Dates for the package executions.

N

This SSIS_Configuration setting determine if manually entered dates are controlling the package executions. If this is set to Y, the Incremental Begin and End Dates will not be updated, but used as entered in the SSIS_Configuration settings.