For Each Loop

Foreach Loop Container

ScriptTask

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.

C:\DW Local Stuff\SQL Data\

This SSIS_Configuration setting contains the folder name where the data file for the DWStaging database is located. This will be used to create a database snapshot prior to running the SSIS packages to update the Enterprise Data Warehouse.

DWStaging

This SSIS_Configuration setting contains the name of the DWStaging database. It will be used to update the Connection Manager for the DWStaging Initial Catalog once the Snapshot has been dropped.

DWStaging_ETL_20170309_174513

@[User::DWStagingDBName] + "_ETL_" + (DT_WSTR,8) ( (YEAR( @[System::ContainerStartTime] ) * 10000) + (MONTH( @[System::ContainerStartTime] ) * 100) + DAY( @[System::ContainerStartTime] ) ) + "_" + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[System::ContainerStartTime] ), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[System::ContainerStartTime] ), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[System::ContainerStartTime] ), 2)

This contains the name of the Snapshot that was created at the start of the control package. It is a concatenation of the name of the DWStaging database and the date and time of the start of the execution of the control package.

The list of files to be attached to the e-mail detailing record counts and data validation issues.

Unable to delete the following snapshots of the DWStaging database: This failure can happen in a user other than the ETL execution account created a snapshot with a name like 'DWStaging_ETL%'.

"Unable to delete the following snapshots of the DWStaging database: " + @[User::SnapshotDropFailList] + " This failure can happen in a user other than the ETL execution account created a snapshot with a name like 'DWStaging_ETL%'."

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.

System.Object

This contains a list of the snapshots that were created.

This contains the name of the snapshot to be deleted.

SELECT d.name AS SnapshotName FROM sys.databases d WHERE d.source_database_id IS NOT NULL AND d.name LIKE 'DWStaging_ETL%'

" SELECT d.name AS SnapshotName FROM sys.databases d WHERE d.source_database_id IS NOT NULL AND d.name LIKE '" + @[User::DWStagingDBName] + "_ETL%' "

This is the SQL statement that retrieve the list of snaphot names that have been created.

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.

DROP DATABASE []

"DROP DATABASE [" + @[User::NameOfSnapshotToDelete] + "]"

This is the SQL statement to drop the Snapshot.

UPDATE [dbo].[SSIS Configurations] SET ConfiguredValue = 'DWStaging' WHERE ConfigurationFilter = 'ConnMgr_DWStaging' AND PackagePath = '\Package.Connections[DWStaging].Properties[InitialCatalog]'

"UPDATE [dbo].[SSIS Configurations] SET ConfiguredValue = '" + @[User::DWStagingDBName] + "' WHERE ConfigurationFilter = 'ConnMgr_DWStaging' AND PackagePath = '\\Package.Connections[DWStaging].Properties[InitialCatalog]' "

This is the SQL statement to update the SSIS_Configuration setting for the DWStaging Connecttion Manager.