2016-03-04

This contains the date of the day the package is executed on. It is a setting from the SSIS_Configuration table.

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.

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.

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.

/* 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.

DWOperations

The name of the Operations Database. This is a configuration setting in SSIS_Configurations.

/* 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.