ssis_t.TblVariable_src - V
type: V ( view ), modify_date: 2021-11-01 13:46:40
RepoObject_guid: 9264FBE4-113B-EC11-852C-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
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;