ssis.Package_src - V

type: V ( view ), modify_date: 2021-11-22 17:33:49

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

Description

Examples

Entity Diagram

entity-ssis.package_src

Columns

Table 1. Columns of ssis.Package_src - V
PK Column Name Data Type NULL? ID

1

varchar(50)

NOT NULL

2

varchar(200)

NULL

datetime

NULL

nvarchar(500)

NULL

varchar(1000)

NULL

nvarchar(max)

NULL

uniqueidentifier

NULL

nvarchar(500)

NULL

int

NULL

nvarchar(500)

NULL

varchar(8000)

NOT NULL

varchar(100)

NULL

varchar(28)

NULL

uniqueidentifier

NULL

varchar(8000)

NOT NULL

int

NOT NULL

Foreign Key Diagram

entity_1_1_fk-ssis.package_src

Indexes

PK_Package_src

idx_Package_src__2

idx_Package_src__3

Column Details

_

PackageCreationDate

PackageCreationDate

datetime

NULL

PackageCreatorComputerName

PackageCreatorComputerName

nvarchar(500)

NULL

PackageCreatorName

PackageCreatorName

varchar(1000)

NULL

PackageDescription

PackageDescription

nvarchar(max)

NULL

PackageDTSID

PackageDTSID

uniqueidentifier

NULL

Referenced Columns
Referencing Columns

PackageLastModifiedProductVersion

PackageLastModifiedProductVersion

nvarchar(500)

NULL

PackageLocaleID

PackageLocaleID

int

NULL

Referenced Columns
Referencing Columns

PackageObjectName

PackageObjectName

nvarchar(500)

NULL

PackagePath

PackagePath

varchar(8000)

NOT NULL

Referenced Columns
Referencing Columns

PackageProtectionLevel

PackageProtectionLevel

varchar(100)

NULL

PackageProtectionLevelName

PackageProtectionLevelName

varchar(28)

NULL

Description

(case [PackageProtectionLevel] when '0' then 'DontSaveSensitive' when '1' then 'EncryptSensitiveWithUserKey' when '2' then 'EncryptSensitiveWithPassword' when '3' then 'EncryptAllWithPassword' when '4' then 'EncryptAllWithUserKey' when '5' then 'ServerStorage' end)


PackageVersionGUID

PackageVersionGUID

uniqueidentifier

NULL

ProjectPath

ProjectPath

varchar(8000)

NOT NULL

Referenced Columns
Referencing Columns

RowID

RowID

int

NOT NULL

Referenced Columns
Referencing Columns

sql_modules_definition

ssis.Package_src - V script
CREATE View ssis.Package_src
As
Select
    proj.AntoraModule
  , p.PackageName
  , p.PackageCreationDate
  , p.PackageCreatorComputerName
  , p.PackageCreatorName
  , p.PackageDescription
  , p.PackageDTSID
  , p.PackageLastModifiedProductVersion
  , p.PackageLocaleID
  , p.PackageObjectName
  , p.PackagePath
  , p.PackageProtectionLevel
  , p.PackageProtectionLevelName
  , p.PackageVersionGUID
  ----The XML data type cannot be compared or sorted, except when using the IS NULL operator.
  --, p.PackageXML
  , p.ProjectPath
  , p.RowID

--, PackageProtectionLevelName =
---- https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.dtsprotectionlevel?view=sqlserver-2019
----DontSaveSensitive	0
----Sensitive information is not saved in the package. The sensitive information is removed and replaced with blanks.

----EncryptAllWithPassword	3
----Encrypts the entire package by using a password.

----EncryptAllWithUserKey	4
----Encrypts the entire package by using keys based on the user profile. Only the same user using the same profile can load the package.

----EncryptSensitiveWithPassword	2
----Encrypts only sensitive information contained in the package by using a password. DPAPI is used for this encryption.

----EncryptSensitiveWithUserKey	1
----Encrypts sensitive properties only by using keys based on the current user. Only the same user using the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks. DPAPI is used for this encryption.

----ServerStorage	5
----Encrypts the package within a SQL Server msdb database. This option is supported only when a package is saved to SQL Server. It is not supported when a package is saved to the File System. The access control of who can decrypt the package is controlled by SQL Server database roles. For more information, see Database-Level Roles and sysssispackages (Transact-SQL).
--Case p.PackageProtectionLevel
--    When '0'
--        Then
--        'DontSaveSensitive'
--    When '1'
--        Then
--        'EncryptSensitiveWithUserKey'
--    When '2'
--        Then
--        'EncryptSensitiveWithPassword'
--    When '3'
--        Then
--        'EncryptAllWithPassword'
--    When '4'
--        Then
--        'EncryptAllWithUserKey'
--    When '5'
--        Then
--        'ServerStorage'
--End
From
    ssis_t.pkgStats  As p
    Inner Join
        ssis.Project As proj
            On
            p.ProjectPath = proj.ProjectPath
Where
    Not Left(p.PackageName, 4) = 'obj\'