DimCurriculumLoad
 SQL Flag Records in DimCurriculum For Deletion
Home  Diagrams  Documents  Images  DimCurriculumLoad  Executables  Connection Managers  Log Providers  Variables 
Current Path:  DimCurriculumLoad   SEQ Remove Obsolete Records From DimCurriculum   SQL Flag Records in DimCurriculum For Deletion
  Properties
Property Value
Name SQL Flag Records in DimCurriculum For Deletion
Description Execute SQL Task
Precedence Executables none
Contrained Executables none
BypassPrepare True
CodePage 1252
Connection DWOperations
CreationName Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
DelayValidation False
Description Execute SQL Task
Disable False
DisableEventHandlers False
EventHandlers System.__ComObject
EventInfos System.__ComObject
ExecutionDuration 0
ExecutionResult 0
ExecutionStatus 1
ExecutionValue
FailPackageOnFailure False
FailParentOnFailure False
ForcedExecutionValue 0
ForceExecutionResult -1
ForceExecutionValue False
ID {d6e46c3a-394d-4b60-becc-e9646b79bdbb}
IsDefaultLocaleID True
IsolationLevel 1048576
IsStoredProcedure False
LocaleID English (United States)
LogEntryInfos System.__ComObject
LoggingMode 0
LoggingOptions System.__ComObject
MaximumErrorCount 1
Name SQL Flag Records in DimCurriculum For Deletion
ParameterBindings Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterBindings
Parent System.__ComObject
ResultSetBindings Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultBindings
ResultSetType 1
SqlStatementSource SET NOCOUNT ON
--CTE (Common Table Expression) resultset contains CurriculumSK and FactTables.
;WITH 
  FactTableBySK As 
 (
    SELECT dep_chk.CurriculumSK
        ,dep_chk.FactTable
    FROM [dbo].[Obsolete_DimCurriculum_DependencyCheck] dep_chk
    )
/*
BEGIN - Provides list of all combinations of LicenseKey and AppointmentTypeUID.
List has the following format LicenseKey:AppointmentTypeUID,LicenseKey:AppointmentTypeUID, (etc.)
*/
SELECT DISTINCT
 FactTableBySK.CurriculumSK
 ,lkp.FactTableConcatenated
INTO #ConcatenatedFactBySK
FROM FactTableBySK 
INNER JOIN 
 (
 --Produce Resultset and Remove Ampersands Escape Sequences that may
    --have been embedded in data.
 SELECT DISTINCT 
  --(STUFF removes the first character which will be a comma after concatenation)
  CurriculumSK
  ,STUFF
   (
    (
                --This section uses the CTE to generate an XML output
    SELECT '; ' + CAST(FactTable AS varchar(50))
     FROM FactTableBySK m2
     WHERE 
     m1.CurriculumSK = m2.CurriculumSK
    FOR XML PATH('')
    )
   ,1
   ,1
   ,''
   ) As FactTableConcatenated
 FROM FactTableBySK m1
 GROUP BY CurriculumSK, FactTable
 ) lkp
ON  (
 lkp.CurriculumSK = FactTableBySK.CurriculumSK
 )
/*
END - Provides list of all combinations of LicenseKey and AppointmentTypeUID.
List has the following format LicenseKey:AppointmentTypeUID,LicenseKey:AppointmentTypeUID, (etc.)
*/
UPDATE obs
SET [DoNotDeleteFlag] = 1,
    [Comments] = 'Referencing Tables: ' + #ConcatenatedFactBySK.FactTableConcatenated       
--SELECT obs.*, #ConcatenatedFactBySK.*
FROM [dbo].[Obsolete_DimCurriculum] obs
INNER JOIN #ConcatenatedFactBySK 
    ON (#ConcatenatedFactBySK.CurriculumSK = obs.CurriculumSK)
SqlStatementSourceType 1
StartTime 3/9/2017 5:32:11 PM
StopTime 3/9/2017 5:32:11 PM
TimeOut 0
TransactionOption 1
VariableDispenser System.__ComObject
Variables System.__ComObject
Version 0
  Variables
Name Value Description
  Event Handlers
Name Description Enabled Fail Package On Failure Fail Parent On Failure
Powered by BI Documenter