Home > Back-end >  Search for a variable value in SSIS packages deployed in SSISDB integration Catalog
Search for a variable value in SSIS packages deployed in SSISDB integration Catalog

Time:03-07

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:

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);

Source : XML Query on msdb.dbo.sysssispackages

  • Related