ssis_t.TblProjectConnection_src - V

type: V ( view ), modify_date: 2021-11-04 09:22:08

RepoObject_guid: 8A9A07F7-C23C-EC11-852D-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssis_t.tblprojectconnection_src

Columns

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

uniqueidentifier

NULL

varchar(max)

NULL

varchar(max)

NULL

varchar(8000)

NOT NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

int

NOT NULL

Foreign Key Diagram

entity_1_1_fk-ssis_t.tblprojectconnection_src

References

Referenced Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-ssis_t.tblprojectconnection_src

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssis_t.tblprojectconnection_src

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssis_t.tblprojectconnection_src

Column Reference Diagram

entity_1_1_colref-ssis_t.tblprojectconnection_src

Column Details

_

ConnectionManagerID

ConnectionManagerID

uniqueidentifier

NULL

ConnectionManagerName

ConnectionManagerName

varchar(max)

NULL

ConnectionManagerType

ConnectionManagerType

varchar(max)

NULL

ConnectionPath

ConnectionPath

varchar(8000)

NOT NULL

ConnectionString

ConnectionString

nvarchar(max)

NULL

ExpressionValue

ExpressionValue

nvarchar(max)

NULL

RowID

RowID

int

NOT NULL

sql_modules_definition

ssis_t.TblProjectConnection_src - V script
CREATE View ssis_t.TblProjectConnection_src
As
With
CTE_ConLevel
As
    (
    Select
        con.RowID
      , con.ConnectionPath
      , ConnectionManagerName = cfnodes.x.value (
                                                    'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ObjectName[1]'
                                                  , 'varchar(max)'
                                                )
      , ConnectionManagerType = cfnodes.x.value (
                                                    'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:CreationName[1]'
                                                  , 'varchar(max)'
                                                )
      --, cfnodes1.y.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ConnectionString[1]', 'varchar(max)')
      , ConnectionStringQry   = cfnodes.x.query ( '.' ).query ( 'declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:ConnectionManager/p1:ObjectData/p1:ConnectionManager' )
      , ExpressionQry         = cfnodes.x.query ( '.' ).query ( 'declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:ConnectionManager/p1:PropertyExpression' )
      , ConnectionManagerID   = cfnodes.x.value (
                                                    'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:DTSID[1]'
                                                  , 'uniqueidentifier'
                                                )
    From
        ssis_t.projectConnection                                                                              As con
        Cross Apply con.ConnectionXML.nodes ( 'declare namespace DTS="www.microsoft.com/SqlServer/Dts";(*)' ) As cfnodes(x)
    )
,
CTE_ConLevelDetail
As
    (
    Select
        pkg.RowID
      , pkg.ConnectionPath
      , pkg.ConnectionManagerName
      , pkg.ConnectionManagerType
      , pkg.ConnectionManagerID
      --,pkg.ConnectionStringQry
      , ConnectionString             =
        (
            Select
                Top 1
                ConnectionString = cfnodes.x.value (
                                                       'declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:ConnectionString[1]'
                                                     , 'nvarchar(max)'
                                                   )
            From
                pkg.ConnectionStringQry.nodes ( './*' ) As cfnodes(x)
        )
      , ExpressionValue              =
        (
            Select
                Top 1
                ExpressionValue = cfnodes.x.value ( '.', 'nvarchar(max)' )
            From
                pkg.ExpressionQry.nodes ( './*' ) As cfnodes(x)
        )
      , RetainSameConnectionProperty =
        (
            Select
                Top 1
                RetainSameConnectionProperty = cfnodes.x.value (
                                                                   'declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:Retain[1]'
                                                                 , 'nvarchar(max)'
                                                               )
            From
                pkg.ConnectionStringQry.nodes ( './*' ) As cfnodes(x)
        )
    From
        CTE_ConLevel As pkg
    )
Select
    ConnectionManagerName = con.ConnectionManagerName
  , con.ConnectionManagerID
  , con.ConnectionPath
  , con.ConnectionManagerType
  , con.ConnectionString
  , con.ExpressionValue
  , con.RowID
From
    CTE_ConLevelDetail As con;