Integration Services Package

Property	Value
Name	DimStudentLoad
Description	This package will load any change to the DimStudent 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. The data will be checked if there are any duplicate records and these will be flagged. The data in the Staged Table will be compared against that in the dimension and will be inserted or updated as required. If any duplicate was found, a notification e-mail will be send to the appropriate staff.
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	SCOWAN\hetgroup
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	59342201-726a-4c23-bbf2-74351c5b6638
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	DontSaveSensitive
Save Checkpoints	False
Suppress Configuration Warnings	False
Suspend Required	False
Transaction Option	Supported
Update Objects	False
Version Build	237
Version Comments
Version Guid	5e50f5e8-6bfd-4f7a-997d-f3c652755264
Version Major	1
Version Minor	0

Name	Type	Description
DFT Load Temp Student Tables for Verification	Data Flow Task	Data Flow Task
DFT Load Temp Student tables from Staging	Data Flow Task	Data Flow Task
SQL Detect and Flag Duplicates	Task	Execute SQL Task
SQL Empty Temp Student Tables	Task	Execute SQL Task
SQL Load New Temp Table to DimStudent	Task	Execute SQL Task
SQL Load SCD1 Temp Table to DimStudent	Task	Execute SQL Task

Name	Description
DWOperations
DWStaging
EnterpriseDW

Name	Description

Name	Value	Expression	Description
User::CurrentDayDate	2016-03-04		This contains the date of the day the package is executed on. It is a setting from the SSIS_Configuration table.
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::GetStudentMods	exec usp_DimStudent_Select 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM' , '2016-03-04'	"exec usp_DimStudent_Select '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "' , '" + @[User::CurrentDayDate] + "'"	This variable contains the string to execute the Stored Procedure that will retrieve the infomation from the Source System.
User::IncrementalBeginDtTm	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.
User::IncrementalEndDtTm	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.
User::NewInsertDimStudent	/* SQL Insert into Staged DimStudent table */ INSERT INTO [EnterpriseDW].[dbo].[DimStudent] ([StudentIdAK] ,[StudentId] ,[FullName] ,[LastName] ,[FirstName] ,[MiddleName] ,[GenderCode] ,[Gender] ,[EthnicityCode] ,[Ethnicity] ,[FirstTimeInCollege] ,[PrimaryLanguageCode] ,[PrimaryLanguage] ,[HighSchoolInstitutionId] ,[HighSchool] ,[HomePhoneNumber] ,[CellPhoneNumber] ,[InstitutionalEmailAddress] ,[PersonalEmailAddress] ,[ImmigrationStatusCode] ,[ImmigrationStatus] ,[ResidencyCode] ,[Residency] ,[ResidencyCountyCode] ,[ResidencyCounty] ,[ResidencyStateCode] ,[ResidencyState] ,[ZipCode] ,[DateOfBirth] ,[FERPARequested] ,[RaceWhite] ,[RaceAfricanAmerican] ,[RacePacificIslander] ,[RaceAsian] ,[RaceAmericanIndian] ,[RaceUnreported] ,[CountryOfBirthCode] ,[CountryOfBirth] ,[CountryOfCitizenshipCode] ,[CountryOfCitizenship] ,[HomeCampusCode] ,[HomeCampus] ,[InitialPrepStatus] ,[VeteranStudent] ,[StudentAthlete] ,[InternationalStudent] ,[RowIsCurrent] ,[RowStartDate] ,[RowEndDate]) SELECT StudentIdAK ,StudentId ,FullName ,LastName ,FirstName ,MiddleName ,GenderCode ,Gender ,EthnicityCode ,Ethnicity ,FirstTimeInCollege ,PrimaryLanguageCode ,PrimaryLanguage ,HighSchoolInstitutionId ,HighSchool ,HomePhoneNumber ,CellPhoneNumber ,InstitutionalEmailAddress ,PersonalEmailAddress ,ImmigrationStatusCode ,ImmigrationStatus ,ResidencyCode ,Residency ,ResidencyCountyCode ,ResidencyCounty ,ResidencyStateCode ,ResidencyState ,ZipCode ,DateOfBirth ,FERPARequested ,RaceWhite ,RaceAfricanAmerican ,RacePacificIslander ,RaceAsian ,RaceAmericanIndian ,RaceUnreported ,CountryOfBirthCode ,CountryOfBirth ,CountryOfCitizenshipCode ,CountryOfCitizenship ,HomeCampusCode ,HomeCampus ,InitialPrepStatus ,VeteranStudent ,StudentAthlete ,InternationalStudent ,RowIsCurrent ,RowStartDate ,RowEndDate FROM [dbo].[Staged_dbo_DimStudent__New]	" /* SQL Insert into Staged DimStudent table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudent] ([StudentIdAK] ,[StudentId] ,[FullName] ,[LastName] ,[FirstName] ,[MiddleName] ,[GenderCode] ,[Gender] ,[EthnicityCode] ,[Ethnicity] ,[FirstTimeInCollege] ,[PrimaryLanguageCode] ,[PrimaryLanguage] ,[HighSchoolInstitutionId] ,[HighSchool] ,[HomePhoneNumber] ,[CellPhoneNumber] ,[InstitutionalEmailAddress] ,[PersonalEmailAddress] ,[ImmigrationStatusCode] ,[ImmigrationStatus] ,[ResidencyCode] ,[Residency] ,[ResidencyCountyCode] ,[ResidencyCounty] ,[ResidencyStateCode] ,[ResidencyState] ,[ZipCode] ,[Da	The SQL statement that will insert the data into the DimStudent table.
User::OperationsDBName	DWOperations		The name of the Operations Database. This is a configuration setting in SSIS_Configurations.
User::SCD1UpdateDimStudent	/* Sample Update Type1 to Dim */ UPDATE [EnterpriseDW].[dbo].[DimStudent] SET StudentIdAK = SCD1.StudentIdAK ,StudentId = SCD1.StudentId ,FullName = SCD1.FullName ,LastName = SCD1.LastName ,FirstName = SCD1.FirstName ,MiddleName = SCD1.MiddleName ,GenderCode = SCD1.GenderCode ,Gender = SCD1.Gender ,EthnicityCode = SCD1.EthnicityCode ,Ethnicity = SCD1.Ethnicity ,FirstTimeInCollege = SCD1.FirstTimeInCollege ,PrimaryLanguageCode = SCD1.PrimaryLanguageCode ,PrimaryLanguage = SCD1.PrimaryLanguage ,HighSchoolInstitutionId = SCD1.HighSchoolInstitutionId ,HighSchool = SCD1.HighSchool ,HomePhoneNumber = SCD1.HomePhoneNumber ,CellPhoneNumber = SCD1.CellPhoneNumber ,InstitutionalEmailAddress = SCD1.InstitutionalEmailAddress ,PersonalEmailAddress = SCD1.PersonalEmailAddress ,ImmigrationStatusCode = SCD1.ImmigrationStatusCode ,ImmigrationStatus = SCD1.ImmigrationStatus ,ResidencyCode = SCD1.ResidencyCode ,Residency = SCD1.Residency ,ResidencyCountyCode = SCD1.ResidencyCountyCode ,ResidencyCounty = SCD1.ResidencyCounty ,ResidencyStateCode = SCD1.ResidencyStateCode ,ResidencyState = SCD1.ResidencyState ,ZipCode = SCD1.ZipCode ,DateOfBirth = SCD1.DateOfBirth ,FERPARequested = SCD1.FERPARequested ,RaceWhite = SCD1.RaceWhite ,RaceAfricanAmerican = SCD1.RaceAfricanAmerican ,RacePacificIslander = SCD1.RacePacificIslander ,RaceAsian = SCD1.RaceAsian ,RaceAmericanIndian = SCD1.RaceAmericanIndian ,RaceUnreported = SCD1.RaceUnreported ,CountryOfBirthCode = SCD1.CountryOfBirthCode ,CountryOfBirth = SCD1.CountryOfBirth ,CountryOfCitizenshipCode = SCD1.CountryOfCitizenshipCode ,CountryOfCitizenship = SCD1.CountryOfCitizenship ,HomeCampusCode = SCD1.HomeCampusCode ,HomeCampus = SCD1.HomeCampus ,InitialPrepStatus = SCD1.InitialPrepStatus ,VeteranStudent = SCD1.VeteranStudent ,StudentAthlete = SCD1.StudentAthlete ,InternationalStudent = SCD1.InternationalStudent FROM [EnterpriseDW].[dbo].[DimStudent] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimStudent__Type1Update] AS SCD1 ON Dim.StudentIdAK = SCD1.StudentIdAK	" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudent] SET StudentIdAK = SCD1.StudentIdAK ,StudentId = SCD1.StudentId ,FullName = SCD1.FullName ,LastName = SCD1.LastName ,FirstName = SCD1.FirstName ,MiddleName = SCD1.MiddleName ,GenderCode = SCD1.GenderCode ,Gender = SCD1.Gender ,EthnicityCode = SCD1.EthnicityCode ,Ethnicity = SCD1.Ethnicity ,FirstTimeInCollege = SCD1.FirstTimeInCollege ,PrimaryLanguageCode = SCD1.PrimaryLanguageCode ,PrimaryLanguage = SCD1.PrimaryLanguage ,HighSchoolInstitutionId = SCD1.HighSchoolInstitutionId ,HighSchool = SCD1.HighSchool ,HomePhoneNumber = SCD1.HomePhoneNumber ,CellPhoneNumber = SCD1.CellPhoneNumber ,InstitutionalEmailAddress = SCD1.InstitutionalEmailAddres	The SQL Statement that will update the rows in the DimStudent Table.

Name

Value

Expression

Description