ssisdb - how to read package data RRS feed

  • Question

  • Hi,

    Is there anyway to view the package xml/data from ssisdb in sql2012/2014? From a bit of research I cant find anyway how to do this.

    I need to get data on many things such as lookups, variables, execute sql task, package connections etc.

    currently i upload package xml from file to DB and read from there.


    Monday, December 19, 2016 3:58 PM

All replies

  • Hi,

    The data as XML exists only in the .dtsx file, and its data in the SSISDB is encypted, so there is no way.




    Monday, December 19, 2016 5:00 PM
  • Hi kiwiNspain,

    There's one way to find the package xml in the legency package deployment, you can find it in the MSDB.

    SELECT id
    	,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
    FROM [msdb].[dbo].[sysssispackages]

    However I don't find any way to get the package in SSISDB. There actually exists one column package_data in internal.packages, but that column is just empty. There's one connect submitted regarding this requirement, however, it somehow was closed.

    So far, that only workaround I'm aware of is saving the package xml when deploying. Check the reply from Albert Zhang in this thread

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Eric__Zhang Tuesday, December 20, 2016 7:09 AM
    Tuesday, December 20, 2016 7:08 AM