September 28, 2012 at 3:27 am
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?
October 1, 2012 at 4:35 pm
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
October 4, 2012 at 4:00 am
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%'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply