ssis_t.TblTask_Dft_Component_inputs_inputColumn_src - V

type: V ( view ), modify_date: 2021-11-07 14:25:58

RepoObject_guid: B5DCAD7F-F73E-EC11-852D-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssis_t.tbltask_dft_component_inputs_inputcolumn_src

Columns

Table 1. Columns of ssis_t.TblTask_Dft_Component_inputs_inputColumn_src - V
PK Column Name Data Type NULL? ID

varchar(100)

NULL

varchar(max)

NULL

varchar(max)

NULL

varchar(max)

NULL

varchar(max)

NULL

xml

NULL

int

NOT NULL

int

NOT NULL

varchar(100)

NULL

varchar(100)

NULL

bit

NULL

varchar(500)

NULL

varchar(max)

NULL

xml

NULL

varchar(max)

NULL

int

NULL

Foreign Key Diagram

entity_1_1_fk-ssis_t.tbltask_dft_component_inputs_inputcolumn_src

References

Object Reference Diagram - 1 1

entity_1_1_objectref-ssis_t.tbltask_dft_component_inputs_inputcolumn_src

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssis_t.tbltask_dft_component_inputs_inputcolumn_src

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssis_t.tbltask_dft_component_inputs_inputcolumn_src

Column Reference Diagram

entity_1_1_colref-ssis_t.tbltask_dft_component_inputs_inputcolumn_src

Indexes

idx_TblTask_Dft_Component_inputs_inputColumn_src__1

Column Details

_

Column_cachedDataType

Column_cachedDataType

varchar(100)

NULL

Column_cachedName

Column_cachedName

varchar(max)

NULL

Column_externalMetadataColumnId

Column_externalMetadataColumnId

varchar(max)

NULL

Column_lineageId

Column_lineageId

varchar(max)

NULL

Column_refId

Column_refId

varchar(max)

NULL

columnscontent

columnscontent

xml

NULL

ControlFlowDetailsRowID

ControlFlowDetailsRowID

int

NOT NULL

DftComponentId

DftComponentId

int

NOT NULL

input_errorOrTruncationOperation

input_errorOrTruncationOperation

varchar(100)

NULL

input_errorRowDisposition

input_errorRowDisposition

varchar(100)

NULL

input_hasSideEffects

input_hasSideEffects

bit

NULL

input_name

input_name

varchar(500)

NULL

input_refId

input_refId

varchar(max)

NULL

nodescontent

nodescontent

xml

NULL

refId

refId

varchar(max)

NULL

RowID

RowID

int

NULL

sql_modules_definition

ssis_t.TblTask_Dft_Component_inputs_inputColumn_src - V script
CREATE View [ssis_t].[TblTask_Dft_Component_inputs_inputColumn_src]
As
Select
    T1.ControlFlowDetailsRowID
  , T1.RowID
  , T1.DftComponentId
  , T1.refId
  , input_refId                       = inputnodes.x.value ( '@refId[1]', 'varchar(max)' )
  , input_errorOrTruncationOperation  = inputnodes.x.value ( '@errorOrTruncationOperation[1]', 'varchar(100)' )
  , input_errorRowDisposition         = inputnodes.x.value ( '@errorRowDisposition[1]', 'varchar(100)' )
  , input_hasSideEffects              = inputnodes.x.value ( '@hasSideEffects[1]', 'bit' )
  , input_name                        = inputnodes.x.value ( '@name[1]', 'varchar(500)' )
  , Column_refId                      = columsnodes.x.value ( '@refId[1]', 'varchar(max)' )
  --, Column_name                       = columsnodes.x.value ( '@name[1]', 'varchar(max)' )
  --, Column_dataType                   = columsnodes.x.value ( '@dataType[1]', 'varchar(100)' )
  --, Column_length                     = columsnodes.x.value ( '@length[1]', 'int' )
  --, Column_precision                  = columsnodes.x.value ( '@precision[1]', 'int' )
  --, Column_scale                      = columsnodes.x.value ( '@scale[1]', 'int' )
  , Column_cachedName                 = columsnodes.x.value ( '@cachedName[1]', 'varchar(max)' )
  , Column_cachedDataType             = columsnodes.x.value ( '@cachedDataType[1]', 'varchar(100)' )
  --, Column_cachedlength               = columsnodes.x.value ( '@cachedlength[1]', 'int' )
  --, Column_cachedprecision            = columsnodes.x.value ( '@cachedprecision[1]', 'int' )
  --, Column_cachedscale                = columsnodes.x.value ( '@cachedscale[1]', 'int' )
  , Column_externalMetadataColumnId   = columsnodes.x.value ( '@externalMetadataColumnId[1]', 'varchar(max)' )
  , Column_lineageId                  = columsnodes.x.value ( '@lineageId[1]', 'varchar(max)' )

  -- to check the nodes content:
  , nodescontent                      = inputnodes.x.query ( '.' )
  , columnscontent                    = columsnodes.x.query ( '.' )
From
    ssis_t.TblTask_Dft_Component                    As T1
    Cross Apply T1.inputsQry.nodes ( './inputs/*' ) As inputnodes(x)
    Outer Apply inputnodes.x.nodes ( './inputColumns/*' ) As columsnodes(x)
Where
    --some outputs are without inputColumns
    Not columsnodes.x.value ( '@refId[1]', 'varchar(max)' ) Is Null