ssis_t.TblTask_Dft_Component_outputs_externalMetadataColumn_src - V

type: V ( view ), modify_date: 2021-11-07 14:21:17

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

Description

Examples

Entity Diagram

entity-ssis_t.tbltask_dft_component_outputs_externalmetadatacolumn_src

Columns

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

varchar(100)

NULL

int

NULL

varchar(max)

NULL

int

NULL

varchar(max)

NULL

int

NULL

xml

NULL

int

NOT NULL

int

NOT NULL

bit

NULL

xml

NULL

varchar(500)

NULL

varchar(max)

NULL

varchar(max)

NULL

int

NULL

Foreign Key Diagram

entity_1_1_fk-ssis_t.tbltask_dft_component_outputs_externalmetadatacolumn_src

References

Object Reference Diagram - 1 1

entity_1_1_objectref-ssis_t.tbltask_dft_component_outputs_externalmetadatacolumn_src

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssis_t.tbltask_dft_component_outputs_externalmetadatacolumn_src

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssis_t.tbltask_dft_component_outputs_externalmetadatacolumn_src

Column Reference Diagram

entity_1_1_colref-ssis_t.tbltask_dft_component_outputs_externalmetadatacolumn_src

Indexes

idx_TblTask_Dft_Component_outputs_externalMetadataColumn_src__1

Column Details

_

Column_dataType

Column_dataType

varchar(100)

NULL

Column_length

Column_length

int

NULL

Column_name

Column_name

varchar(max)

NULL

Column_precision

Column_precision

int

NULL

Column_refId

Column_refId

varchar(max)

NULL

Column_scale

Column_scale

int

NULL

columnscontent

columnscontent

xml

NULL

ControlFlowDetailsRowID

ControlFlowDetailsRowID

int

NOT NULL

DftComponentId

DftComponentId

int

NOT NULL

externalMetadataColumns_isused

externalMetadataColumns_isused

bit

NULL

nodescontent

nodescontent

xml

NULL

output_name

output_name

varchar(500)

NULL

output_refId

output_refId

varchar(max)

NULL

RowID

RowID

int

NULL

sql_modules_definition

ssis_t.TblTask_Dft_Component_outputs_externalMetadataColumn_src - V script
CREATE View [ssis_t].[TblTask_Dft_Component_outputs_externalMetadataColumn_src]
As
Select
    T1.ControlFlowDetailsRowID
  , T1.RowID
  , T1.DftComponentId
  , T1.refId
  , output_refId                      = outputnodes.x.value ( '@refId[1]', 'varchar(max)' )
  , output_name                       = outputnodes.x.value ( '@name[1]', 'varchar(500)' )
  , externalMetadataColumns_isused    = outputnodes.x.value ( 'data(./externalMetadataColumns/@isUsed)[1]', 'bit' )
  , 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_errorOrTruncationOperation = columsnodes.x.value ( '@errorOrTruncationOperation[1]', 'varchar(100)' )
  --, Column_errorRowDisposition        = columsnodes.x.value ( '@errorRowDisposition[1]', 'varchar(100)' )
  --, Column_externalMetadataColumnId   = columsnodes.x.value ( '@externalMetadataColumnId[1]', 'varchar(max)' )
  --, Column_lineageId                  = columsnodes.x.value ( '@lineageId[1]', 'varchar(max)' )
  --, Column_truncationRowDisposition   = columsnodes.x.value ( '@truncationRowDisposition[1]', 'varchar(max)' )

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