Filters:

  • Technologies

  • Instructors

  • Extracting SSIS Package information using T-SQL

    SQL Server Integration Services can store it packages in either the file system or the MSDB system database.  One of the advantages of storing a package in MSDB is that you can use T-SQL to read the package definition and generate reports on your SSIS package library.

    Here are a couple of example queries to extract the package GUID for each package and to display the connection string for each OLEDB connection manager in a package.

    Example 1: Package GUID display

     


    USE MSDB
    GO
    WITH xmlPackages
    -- Read sysssispackage and convert packagedata image column to XML datatype
    AS
    (SELECT [name]
    , [Description]
    , verbuild
    , CAST(CAST(packagedata as VARBINARY(MAX)) AS XML) PackageXML
    FROM dbo.sysssispackages)
    ,
    Packages
    -- Cross join each package row with itself to extract out the GUID then convert it to VARCHAR

    AS
    (SELECT [name]
    , [Description]
    , [verbuild]
    , x.guid.value('text()[1]', 'varchar(4000)') PackageGUID
    FROM xmlPackages
    CROSS APPLY
    PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; //DTS:Executable/DTS:Property[@DTS:Name="VersionGUID"]') AS x(guid)
    )

    SELECT *
    FROM Packages

     

    Example 2: Display Connections strings for each OLEDB Connection Manager
     

    USE MSDB
    GO
    WITH xmlPackages
    -- Read sysssispackages and convert packagedata image column to XML datatype
    AS
    (SELECT [name]
    , [Description]
    , verbuild
    , CAST(CAST(packagedata as VARBINARY(MAX)) AS XML) PackageXML
    FROM dbo.sysssispackages)
    ,
    Packages
    -- Cross join each package row with OLEDB connections found in the package and convert it to VARCHAR
    AS
    (SELECT [name]
    , [Description]
    , [verbuild]
    , x.cn.value('text()[1]', 'varchar(4000)') Conn
    FROM xmlPackages
    CROSS APPLY
    PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; //DTS:ConnectionManager[DTS:Property="OLEDB"]/DTS:ObjectData/DTS:ConnectionManager/DTS:Property [@DTS:Name="ConnectionString"]') AS x(cn)
    )
    SELECT [Name] PackageName
    , [Description]
    , [verbuild]
    , Conn ConnectionString
    FROM Packages


     

    You may also like:  How to Display the Elapsed Processing Time of a Report in SSRS

    See what people are saying...

    1. Thomas

      Thank you. This was very helpful. I am trying to find the Child Packages a package calls. I cannot figure out the syntax to get what is in Arguments

      Any help is appreciated. Thank you

    2. Jacob

      Found it! When copying the code make sure it uses correct Invalid character for quote ‘
      Also comments have to be removed or re-keyed.
      Thank you very much for your help!

    3. Jacob

      It looks like I is giving an error message: “Msg 2333, Level 16, State 1, Line 8
      XQuery [#xmlPackages.PackageXML.nodes()]: Invalid source character 0x201d”
      Can you help please?

    4. Interface Technical Training

      Glad you found it useful. Both examples are the SQL code. The line you referenced is a comment. These SQL statements will would with SQL Server 2005 or later.

    5. Jacob

      Thank you for your article. Could you please also include the code for ” Read sysssispackage and convert packagedata image column to XML datatype” .
      Is this code SQL version specific?

    Share your thoughts...

    Please fill out the comment form below to post a reply.