How to find packages which include a specific database as a datasource

  • Problem : Database needs to move from one server to another.

    Before moving the database, I would need to get a list of all the ssis packages which reference this DB so that they can be updated after the move.

    Is there a table in MSDB which has this information?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • I guess you are not using SSIS Package Configurations to store Connection Manager info. If you are you can simply change the config files and the code will not need to change.

    If not, then you can try selecting this information from the package XML:

    SELECT name

    FROM msdb.dbo.sysdtspackages90

    WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

    LIKE 'servername\instancename';

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • This seems to work well.

    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,

    'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespaces

    SELECT c.name,

    SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',

    'varchar(100)') AS ObjectName,

    SSIS_XML.value('./pNS1:Property[@pNS1:Name="Description"][1]',

    'varchar(100)') AS Description,

    SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]

    /pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionString

    FROM

    --

    ( SELECT id ,

    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML

    FROM [msdb].[dbo].[sysdtspackages90]

    ) PackageXML

    CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML ( SSIS_XML )

    INNER JOIN [msdb].[dbo].[sysdtspackages90] c ON PackageXML.id = c.id

    WHERE SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]

    /pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') LIKE '%data source%'

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply