ssis_t.TblTask_Dft_Details_src - V

/*
data flow task

details per each column between data flow task steps

data flow task steps can be extracted later by using select distinct
*/
CREATE VIEW [ssis_t].[TblTask_Dft_Details_src]
As
Select
    pkglvl.ControlFlowDetailsRowID
  , pkglvl.RowID
  , DFTTasksPath                  = dftnodes.x.value ( '@refId[1]', 'varchar(max)' )
  , DFTTaskName                   = dftnodes.x.value ( '@name[1]', 'varchar(max)' )
  , DFTTaskType                   = dftnodes.x.value ( '@description[1]', 'varchar(max)' )
  , DFTTaskType1                  = dftnodes.x.value ( '@contactInfo[1]', 'varchar(max)' )
  , DFTRowSet                     = dftnodes.x.value ( 'data(./properties/property[@name=''OpenRowset'']	)[1]', 'varchar(max)' )
  , ParameterBindingParameterName = dftnodes.x.value (
                                                         'data(./properties/property[@name=''ParameterMapping'']	)[1]'
                                                       , 'varchar(max)'
                                                     )
  , DFTSQLCommand                 = Case
                                        When IsNull (
                                                        dftnodes.x.value (
                                                                             'data(./properties/property[@name=''SqlCommand''])[1]'
                                                                           , 'varchar(max)'
                                                                         )
                                                      , ''
                                                    ) = ''
                                            Then
                                            IsNull (
                                                       dftnodes.x.value (
                                                                            'data(./properties/property[@name=''SqlCommandVariable''])[1]'
                                                                          , 'varchar(max)'
                                                                        )
                                                     , ''
                                                   )
                                        Else
                                            IsNull (
                                                       dftnodes.x.value (
                                                                            'data(./properties/property[@name=''SqlCommand''])[1]'
                                                                          , 'varchar(max)'
                                                                        )
                                                     , ''
                                                   )
                                    End
  , DFTConnectionManager          = dftnodes.x.value ( 'data(./connections/connection/@connectionManagerID)[1]', 'varchar(max)' )
  , Variable                      = dftnodes.x.value ( 'data(./properties/property[@name=''VariableName'']	)[1]' , 'varchar(max)' )
  , IsSortedProperty              = dftnodes.x.value ( 'data(./outputs/output/@isSorted)[1]', 'varchar(10)' )
  --multiple input and output columns per data flow step:
  , InputQry                      = lineage.x.query ( '.' )
  , OutputQry                     = outputvalue.x.query ( '.' )
  , MultihashcolumnSortPosition   = outputvalue.x.value (
                                                            'data(./properties/property[@name=''InputColumnLineageIDs'']	)[1]'
                                                          , 'varchar(max)'
                                                        )
From
    ssis_t.TblControlFlow                       As pkglvl
    Cross Apply pkglvl.DFTQuery.nodes ( './*' ) As dftnodes(x)
    Outer Apply dftnodes.x.nodes ( './inputs/input/inputColumns/*' ) As lineage(x)
    Outer Apply dftnodes.x.nodes ( './outputs/output/outputColumns/*' ) As outputvalue(x);