ssis_t.TblTask_Dft_Component_outputs_outputColumn_src - V

type: V ( view ), modify_date: 2021-11-07 12:43:14

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

Description

Examples

Entity Diagram

entity-ssis_t.tbltask_dft_component_outputs_outputcolumn_src

Columns

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

varchar(100)

NULL

varchar(100)

NULL

varchar(100)

NULL

varchar(max)

NULL

int

NULL

varchar(max)

NULL

varchar(max)

NULL

int

NULL

varchar(max)

NULL

int

NULL

varchar(max)

NULL

xml

NULL

int

NOT NULL

int

NOT NULL

xml

NULL

bit

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_outputcolumn_src

References

Object Reference Diagram - 1 1

entity_1_1_objectref-ssis_t.tbltask_dft_component_outputs_outputcolumn_src

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssis_t.tbltask_dft_component_outputs_outputcolumn_src

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssis_t.tbltask_dft_component_outputs_outputcolumn_src

Column Reference Diagram

entity_1_1_colref-ssis_t.tbltask_dft_component_outputs_outputcolumn_src

Indexes

idx_TblTask_Dft_Component_outputs_outputColumn_src__1

Column Details

_

Column_dataType

Column_dataType

varchar(100)

NULL

Column_errorOrTruncationOperation

Column_errorOrTruncationOperation

varchar(100)

NULL

Column_errorRowDisposition

Column_errorRowDisposition

varchar(100)

NULL

Column_externalMetadataColumnId

Column_externalMetadataColumnId

varchar(max)

NULL

Column_length

Column_length

int

NULL

Column_lineageId

Column_lineageId

varchar(max)

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

Column_truncationRowDisposition

Column_truncationRowDisposition

varchar(max)

NULL

columnscontent

columnscontent

xml

NULL

ControlFlowDetailsRowID

ControlFlowDetailsRowID

int

NOT NULL

DftComponentId

DftComponentId

int

NOT NULL

nodescontent

nodescontent

xml

NULL

output_isErrorOut

output_isErrorOut

bit

NULL

output_name

output_name

varchar(500)

NULL

output_refId

output_refId

varchar(max)

NULL

refId

refId

varchar(max)

NULL

RowID

RowID

int

NULL

sql_modules_definition

ssis_t.TblTask_Dft_Component_outputs_outputColumn_src - V script
CREATE View ssis_t.TblTask_Dft_Component_outputs_outputColumn_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)' )
  , output_isErrorOut                 = outputnodes.x.value ( '@isErrorOut[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 ( './outputColumns/*' ) As columsnodes(x)
Where
    --some outputs are without outputColumns
    Not columsnodes.x.value ( '@refId[1]', 'varchar(max)' ) Is Null