ssis_t.TblConnection_src - V

type: V ( view ), modify_date: 2021-11-01 13:46:40

RepoObject_guid: 7D64FBE4-113B-EC11-852C-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssis_t.tblconnection_src

Columns

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

varchar(10)

NOT NULL

uniqueidentifier

NULL

varchar(max)

NULL

varchar(max)

NULL

varchar(max)

NULL

varchar(2)

NOT NULL

varchar(max)

NULL

varchar(1000)

NULL

varchar(8000)

NULL

varchar(8000)

NOT NULL

nvarchar(max)

NULL

int

NOT NULL

Foreign Key Diagram

entity_1_1_fk-ssis_t.tblconnection_src

References

Referenced Objects

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-ssis_t.tblconnection_src

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssis_t.tblconnection_src

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssis_t.tblconnection_src

Column Reference Diagram

entity_1_1_colref-ssis_t.tblconnection_src

Column Details

_

Category

Category

varchar(10)

NOT NULL

ConnectionManagerID

ConnectionManagerID

uniqueidentifier

NULL

ConnectionManagerName

ConnectionManagerName

varchar(max)

NULL

ConnectionManagerType

ConnectionManagerType

varchar(max)

NULL

ConnectionString

ConnectionString

varchar(max)

NULL

DelayValidationPropertyValue

DelayValidationPropertyValue

varchar(2)

NOT NULL

ExpressionValue

ExpressionValue

varchar(max)

NULL

PackageCreatorName

PackageCreatorName

varchar(1000)

NULL

PackageName

PackageName

varchar(8000)

NULL

PackagePath

PackagePath

varchar(8000)

NOT NULL

RetainSameConnectionProperty

RetainSameConnectionProperty

nvarchar(max)

NULL

RowID

RowID

int

NOT NULL

sql_modules_definition

ssis_t.TblConnection_src - V script
CREATE VIEW [ssis_t].[TblConnection_src]
As
With
CTE_PkgLevel
As
    (
    Select
        pkg.RowID
      , pkg.PackagePath
      --add PackageName
      , pkg.PackageName
      , 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'
                                                )
      , pkg.PackageCreatorName
    From
        ssis_t.pkgStats                                                                                                                            As pkg
        Cross Apply pkg.PackageXML.nodes ( 'declare namespace DTS="www.microsoft.com/SqlServer/Dts";(//DTS:Executable/DTS:ConnectionManagers/*)' ) As cfnodes(x)
    )
,
CTE_PkgConLevel
As
    (
    Select
        pkg.RowID
      , pkg.PackagePath
      , pkg.PackageName
      , 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)
        )
      , pkg.PackageCreatorName
      , 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_PkgLevel As pkg
    )
--Insert Into ssis.PackageAnalysis
--(
--    RowID
--  , PackagePath
--  , PackageName
--  , Category
--  , PackageCreatorName
--  , ObjectName
--  , ObjectType
--  , DelayValidationPropertyValue
--  , ObjectValue
--  , ExpressionValue
--  , ConnectionManagerID
--  , RetainSameConnectionProperty
--)
Select
    Distinct
    pkg.RowID
  , pkg.PackagePath
  --, PackageName                  = Replace ( pkg.PackagePath, Replace ( @Path, '*.dtsx', '' ), '' )
  , pkg.PackageName
  , Category                     = 'Connection'
  , pkg.PackageCreatorName
  , ConnectionManagerName        = Cast(pkg.ConnectionManagerName As Varchar(Max))
  , ConnectionManagerType        = Cast(pkg.ConnectionManagerType As Varchar(Max))
  , DelayValidationPropertyValue = 'NA'
  , ConnectionString             = Cast(pkg.ConnectionString As Varchar(Max))
  , ExpressionValue              = Cast(pkg.ExpressionValue As Varchar(Max))
  , pkg.ConnectionManagerID
  , pkg.RetainSameConnectionProperty
From
    CTE_PkgConLevel As pkg;