ssis_t.TblVariable_src - V

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

RepoObject_guid: 9264FBE4-113B-EC11-852C-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssis_t.tblvariable_src

Columns

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

varchar(8)

NOT NULL

varchar(max)

NOT NULL

varchar(1000)

NULL

varchar(8000)

NULL

varchar(8000)

NOT NULL

int

NOT NULL

varchar(max)

NULL

varchar(max)

NULL

varchar(max)

NULL

varchar(max)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-ssis_t.tblvariable_src

References

Referenced Objects

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-ssis_t.tblvariable_src

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssis_t.tblvariable_src

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssis_t.tblvariable_src

Column Reference Diagram

entity_1_1_colref-ssis_t.tblvariable_src

Column Details

_

Category

Category

varchar(8)

NOT NULL

ExpressionValue

ExpressionValue

varchar(max)

NOT NULL

PackageCreatorName

PackageCreatorName

varchar(1000)

NULL

PackageName

PackageName

varchar(8000)

NULL

PackagePath

PackagePath

varchar(8000)

NOT NULL

RowID

RowID

int

NOT NULL

VariableDataType

VariableDataType

varchar(max)

NULL

VariableHexValue

VariableHexValue

varchar(max)

NULL

VariableName

VariableName

varchar(max)

NULL

VariableValue

VariableValue

varchar(max)

NOT NULL

sql_modules_definition

ssis_t.TblVariable_src - V script
CREATE VIEW [ssis_t].[TblVariable_src]
As
With
CTE_PkgLevel
As
    (
    Select
        pkg.RowID
      , pkg.PackagePath
      --add PackageName:
      , pkg.PackageName
      , VariableName     = cfnodes.x.value (
                                               'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ObjectName[1]'
                                             , 'varchar(max)'
                                           )
      , VariableHexValue = cfnodes.x.value (
                                               'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:DTSID[1]'
                                             , 'varchar(max)'
                                           )
      , ExpressionValue  = cfnodes.x.value (
                                               'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Expression[1]'
                                             , 'varchar(max)'
                                           )
      , VariableQry      = cfnodes.x.query ( '.' ).query ( 'declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:Variable/p1:VariableValue' )
      , pkg.PackageCreatorName
    From
        ssis_t.pkgStats                                                                                                                     As pkg
        Cross Apply pkg.PackageXML.nodes ( 'declare namespace DTS="www.microsoft.com/SqlServer/Dts";(//DTS:Executable/DTS:Variables/*)' ) As cfnodes(x)
    )
--select * from CTE_PkgLevel
,
CTE_PkgVarLevel
As
    (
    Select
        pkg.RowID
      , pkg.PackagePath
      --add PackageName:
      , pkg.PackageName
      , pkg.VariableName
      , pkg.ExpressionValue
      , pkg.VariableHexValue
      , VariableValue    =
        (
            Select
                Top 1
                VariableValue = cfnodes.x.value ( '.', 'nvarchar(max)' )
            From
                pkg.VariableQry.nodes ( './*' ) As cfnodes(x)
        )
      --,pkg.ConnectionStringQry
      , VariableDataType =
        (
            Select
                Top 1
                DataType = cfnodes.x.value (
                                               'declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:DataType[1]'
                                             , 'nvarchar(max)'
                                           )
            From
                pkg.VariableQry.nodes ( './*' ) As cfnodes(x)
        )
      , pkg.PackageCreatorName
    From
        CTE_PkgLevel As pkg
    )
--Insert Into ssis.TblVariableDetails
--(
--    RowID
--  , PackagePath
--  , PackageName
--  , Category
--  , PackageCreatorName
--  , ObjectName
--  , ObjectType
--  , ObjectValue
--  , ExpressionValue
--  , VariableHexValue
--)
Select
    Distinct
    Tblvar.RowID
  , Tblvar.PackagePath
  , Tblvar.PackageName
  --, PackageName     = Replace ( Tblvar.PackagePath, Replace ( @Path, '*.dtsx', '' ), '' )
  , Category        = 'Variable'
  , Tblvar.PackageCreatorName
  , VariableName        = Cast(Tblvar.VariableName As Varchar(Max))
  , VariableDataType        = Cast(Tblvar.VariableDataType As Varchar(Max))
  , VariableValue   = IsNull ( Cast(Tblvar.VariableValue As Varchar(Max)), '' )
  , ExpressionValue = IsNull ( Cast(Tblvar.ExpressionValue As Varchar(Max)), '' )
  , Tblvar.VariableHexValue
From
    CTE_PkgVarLevel As Tblvar;