I have several packages/projects deployed in the SSISDB catalog. I want to find which packages have a specific expression attached to a variable value. Is there a TSQL way of doing this? I know the package data is encrypted in [internal].[packages]. But with the proper credentials, is it possible to decrypt it? Or what are the other options I have? Can I use some C#/Powershell script to search?
CodePudding user response：
There is no tables that stores this kind of information. You should search for it by reading the package XML. Using SSISDB, it is not possible to read the package XML using T-SQL since SSISDB encrypts the whole project as binary.
To read the package's XML, you should extract the project binary using the SSISDB.cataloag.get_project stored procedure. Change the extract file extension to .zip and extract its content. Then, loop over packages to check if the variable is used in each package. This can be done using C# or PowerShell:
- Extracting dtsx from Integration Service Catalog from C#
- Get Package XML from SSIS Catalog with PowerShell
In case you are storing the database within SQL Server (Msdb) you can read the package XML data from the msdb.dbo.sysssispackages table.
CodePudding user response：
You can refer to sysssispackages which ontains one row for each package that is saved to Microsoft SQL Server. This table is stored in the msdb database.
Otherwise you can use XML Query on msdb.dbo.sysssispackages. Here is an example :
DECLARE @DTSXML XML = N'<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:refId="Package"
DTS:CreationDate="11/7/2013 12:57:04 PM"
DTS:CreationName="SSIS.Package.3"
DTS:CreatorComputerName="USER-COMPUTER"
DTS:CreatorName="MyUserName"
DTS:DTSID="{10A38C50-3656-42E0-A054-87E8D9DFBD8A}"
DTS:ExecutableType="SSIS.Package.3"
DTS:LastModifiedProductVersion="11.0.2100.60"
DTS:LocaleID="2057"
DTS:ObjectName="TEST_RELOAD_PACKAGE_DDL_TEST_RELOAD"
DTS:VersionGUID="{9C72489E-4800-4E59-9666-6B3FA0A664F9}">
<DTS:Property
DTS:Name="PackageFormatVersion">6</DTS:Property>
<DTS:ConnectionManagers>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[DESTINATION_CONN]"
DTS:CreationName="OLEDB"
DTS:DTSID="{1FD5677D-2669-41DB-83DB-CB8FE1C89246}"
DTS:ObjectName="DESTINATION_CONN">
<DTS:ObjectData>
<DTS:ConnectionManager />
</DTS:ObjectData>
</DTS:ConnectionManager>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[EXEC_CONN]"
DTS:CreationName="OLEDB"
DTS:DTSID="{B81FCD2E-0AA9-45F3-8C8C-39675857B93F}"
DTS:ObjectName="EXEC_CONN">
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=MyServerInstance;Initial Catalog=MyDatabaseInstance;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SR_PROC_CTRL;" />
</DTS:ObjectData>
</DTS:ConnectionManager>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[LOG_CONN]"
DTS:CreationName="OLEDB"
DTS:DTSID="{9D9716EF-0E06-4BDF-89DA-B8EA03A0871F}"
DTS:ObjectName="LOG_CONN">
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=MyServerInstance;Initial Catalog=MyDatabaseInstance;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SR_PROC_CTRL;" />
</DTS:ObjectData>
</DTS:ConnectionManager>
</DTS:ConnectionManagers>
<DTS:LogProviders>
<DTS:LogProvider
DTS:ConfigString="LOG_CONN"
DTS:CreationName="DTS.LogProviderSQLServer"
DTS:DTSID="{81B7AF3A-22F7-4FC8-A3F8-9034F8B241A0}"
DTS:ObjectName="LogToSQLServer">
<DTS:ObjectData>
<InnerObject />
</DTS:ObjectData>
</DTS:LogProvider>
</DTS:LogProviders>
<DTS:Variables />
<DTS:LoggingOptions
DTS:FilterKind="0"
DTS:LoggingMode="1">
<DTS:Property
DTS:DataType="8"
DTS:Name="EventFilter">4,12,OnTaskFailed,10,OnProgress,7,OnError,9,OnWarning</DTS:Property>
<DTS:SelectedLogProviders>
<DTS:SelectedLogProvider
DTS:InstanceID="{81B7AF3A-22F7-4FC8-A3F8-9034F8B241A0}" />
</DTS:SelectedLogProviders>
</DTS:LoggingOptions>
<DTS:Executables>
<DTS:Executable
DTS:refId="Package\EXEC__SRSTG___USP_HOTELS_"
DTS:CreationName="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
DTS:DTSID="{7401EF27-6D72-4E6A-A294-D88CDA1176B6}"
DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
DTS:LocaleID="-1"
DTS:ObjectName="EXEC__SRSTG___USP_HOTELS_"
DTS:ThreadHint="0">
<DTS:Variables />
<DTS:ObjectData>
<SQLTask:SqlTaskData
SQLTask:Connection="{B81FCD2E-0AA9-45F3-8C8C-39675857B93F}"
SQLTask:SqlStatementSource="SELECT * FROM [SRSTG].[TBL_HOTELS];" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
</DTS:ObjectData>
</DTS:Executable>
</DTS:Executables>
</DTS:Executable>'
;WITH XMLNAMESPACES(
'www.microsoft.com/SqlServer/Dts' AS DTS,
DEFAULT 'www.microsoft.com/SqlServer/Dts'
)
SELECT
EXN.R.value('@DTS:refId' ,'NVARCHAR(128)') AS PKG_refId
,EXN.R.value('@DTS:CreationDate' ,'DATETIME2(0)' ) AS PKG_CreationDate
,EXN.R.value('@DTS:CreationName' ,'NVARCHAR(128)') AS PKG_CreationName
,EXN.R.value('@DTS:CreatorComputerName' ,'NVARCHAR(128)') AS PKG_CreatorComputerName
,EXN.R.value('@DTS:CreatorName' ,'NVARCHAR(128)') AS PKG_CreatorName
,EXN.R.value('@DTS:DTSID' ,'NVARCHAR(128)') AS PKG_DTSID
,EXN.R.value('@DTS:ExecutableType' ,'NVARCHAR(128)') AS PKG_ExecutableType
,EXN.R.value('@DTS:LastModifiedProductVersion' ,'NVARCHAR(128)') AS PKG_LastModifiedProductVersion
,EXN.R.value('@DTS:LocaleID' ,'NVARCHAR(128)') AS PKG_LocaleID
,EXN.R.value('@DTS:ObjectName' ,'NVARCHAR(128)') AS PKG_ObjectName
,EXN.R.value('@DTS:VersionGUID' ,'NVARCHAR(128)') AS PKG_VersionGUID
,EXN.R.value('local-name(.)' ,'NVARCHAR(128)') AS LName
,EXN.R.value('.','NVARCHAR(MAX)') AS LValue
,CON.MAN.value('@DTS:refId' ,'NVARCHAR(128)') AS CNM_refId
,CON.MAN.value('@DTS:CreationName' ,'NVARCHAR(128)') AS CNM_CreationName
,CON.MAN.value('@DTS:DTSID' ,'NVARCHAR(128)') AS CNM_DTSID
,CON.MAN.value('@DTS:ObjectName' ,'NVARCHAR(128)') AS CNM_ObjectName
FROM @DTSXML.nodes('/DTS:Executable') AS EXN(R)
OUTER APPLY EXN.R.nodes('DTS:ConnectionManagers/DTS
:ConnectionManager') AS CON(MAN);