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
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_TblTask_Dft_Component_outputs_outputColumn_src__1
-
IndexSemanticGroup: no_group
-
PK, Unique, Real: 0, 0, 0
Column Details
_
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