Integration Services Package

Property	Value
Name	DimUserLoad
Description	This package will load any change to the DimUser dimension. The first step in this package is to truncate all the working tables needed to track new and updated records. The next step will load the data into a staged table in DWOperations.
Checkpoint File Name
Checkpoint Usage	Never
Check Signature On Load	True
Creation Date	Tuesday, May 08, 2012 4:11 PM
Creator Computer Name	scowan
Creator Name	ad\scowan
Debug Mode	False
Delay Validation	False
Disable	False
Disable Event Handlers	False
Enable Configurations	True
Encrypt Checkpoints	False
Fail Package On Failure	False
Fail Parent On Failure	False
Forced Execution Value	0
Force Execution Value	None
Description	False
ID	3e36eef9-0154-4df0-a39f-e34b4d3ac3e4
Interactive Mode	False
Is Default Locale ID	False
Isolation Level	Serializable
Locale ID	1033
Logging Mode	Enabled
Max Concurrent Executables	-1
Maximum Error Count	1
Offline Mode	False
Package Type	DTSDesigner100
Protection Level	EncryptSensitiveWithUserKey
Save Checkpoints	False
Suppress Configuration Warnings	False
Suspend Required	False
Transaction Option	Supported
Update Objects	False
Version Build	291
Version Comments
Version Guid	ca706174-4bc7-42ef-883b-3c6d2f83fb0f
Version Major	1
Version Minor	0

Name	Type	Description
DFT Load Temp User Table for Verification	Data Flow Task	Data Flow Task
DFT Load Temp User Tables from Staging	Data Flow Task	Data Flow Task
SEQ Alert System	Sequence	Sequence Container
SEQ Remove Obsolete Records From DimUser	Sequence	Sequence Container
SQL Detect and Flag Duplicates	Task	Execute SQL Task
SQL Empty Temp User Tables	Task	Execute SQL Task
SQL Load New Temp Table to DimUser	Task	Execute SQL Task
SQL Load SCD1 Temp Table to DimUser	Task	Execute SQL Task

Name	Description
DWOperations
DWStaging
EnterpriseDW

Name	Description

Name	Value	Expression	Description
User::AlertSystemEmailEnabled	Y		Configuration setting in SSIS_Configurations that determines if Alerts will be send via e-mail in case of an abend or data problems
User::DeleteFromDimension	DELETE FROM [EnterpriseDW] .[dbo].[DimUser] WHERE UserSK IN ( SELECT UserSK FROM [dbo].[Obsolete_DimUser] obs WHERE obs.DoNotDeleteFlag = 0 )	" DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "] .[dbo].[DimUser] WHERE UserSK IN ( SELECT UserSK FROM [dbo].[Obsolete_DimUser] obs WHERE obs.DoNotDeleteFlag = 0 ) "	This is the SQL Statement that will be used to delete records from the DimGlCode if they were deleted from the Source System.
User::Domain			Configuration setting from SSIS_Configuration to specify the domain where the e-mail server is running.
User::EDWDBName	EnterpriseDW		Configuration setting in SSIS_Configurations that specifies the name of the Enterprise Data Warehouse.
User::EDWLinkedServerName	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.
User::EmailAttachmentList			The list of files to be attached to the e-mail detailing record counts and data validation issues.
User::EmailBodyText	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.
User::EmailFileLocation	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.
User::EmailFrom	help@hetgroup.org		Configuration setting from SSIS_Configurations that will specify the From E-mail address to be used in e-mail notifications.
User::EmailPassword	datawarehouse		The configuration setting from SSIS_Configurations that specify the password used for the e-mail server when sending e-mail notifications.
User::EmailTo	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.
User::EmailUserName	windows		The configuration setting from SSIS_Configurations that specify the userid used for the e-mail server when sending e-mail notifications.
User::NewInsertDimUser	/* SQL INSERT into DimUser from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimUser] ([DomainUserId] ,[ApplicationUserId] , UserName ,[RowIsCurrent] ,[RowStartDate] ,[RowEndDate]) SELECT [DomainUserId] ,[ApplicationUserId] , UserName ,[RowIsCurrent] ,[RowStartDate] ,[RowEndDate] FROM [dbo].[Staged_dbo_DimUser__New]	" /* SQL INSERT into DimUser from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimUser] ([DomainUserId] ,[ApplicationUserId] , UserName ,[RowIsCurrent] ,[RowStartDate] ,[RowEndDate]) SELECT [DomainUserId] ,[ApplicationUserId] , UserName ,[RowIsCurrent] ,[RowStartDate] ,[RowEndDate] FROM [dbo].[Staged_dbo_DimUser__New] "	This variable contains the string that will be used to insert any new rows into the DimOrgUnit table.
User::RowCount	0		The number of rows that were loaded into the Dimension. This is will be compared against the row count from the source system.
User::SCD1UpdateDimUser	/* Sample Update Type1 to Dim */ UPDATE [EnterpriseDW].[dbo].[DimUser] SET DomainUserId = SCD1.DomainUserId ,ApplicationUserId = SCD1.ApplicationUserId ,UserName = SCD1.UserName ,RowIsCurrent = SCD1.RowIsCurrent ,RowStartDate = SCD1.RowStartDate ,RowEndDate = SCD1.RowEndDate FROM [EnterpriseDW].[dbo].[DimUser] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimUser__Type1Update] AS SCD1 ON Dim.ApplicationUserId = SCD1.ApplicationUserId	" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimUser] SET DomainUserId = SCD1.DomainUserId ,ApplicationUserId = SCD1.ApplicationUserId ,UserName = SCD1.UserName ,RowIsCurrent = SCD1.RowIsCurrent ,RowStartDate = SCD1.RowStartDate ,RowEndDate = SCD1.RowEndDate FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimUser] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimUser__Type1Update] AS SCD1 ON Dim.ApplicationUserId = SCD1.ApplicationUserId "	The SQL string that will update any changed rows in the DimOrgUnit dimension.
User::SingleEmailAttachment	c:\stubfile.txt		Temporary file name that will be used to create the e-mail notifications. This is a configuration setting from SSIS_Configurations.
User::SMTPPort	25		The SMTPPort for the e-mail server to be used when sending notification e-mails. This is a configuration setting from SSIS_Configurations.
User::SMTPServerName	www.ad.hetgroup.org		The SMTP Server Name contains the name for the e-mail server. This is a configuration setting from SSIS_Configurations.
User::TotalRowSource	0		This is the Row count from the Source System to compare against that of the DimCurriculum table to ensure that all records were copied.

Name

Value

Expression

Description