Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find packages which include a specific database as a datasource Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 3:27 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 8:40 AM
Points: 51, Visits: 316
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?
Post #1365710
Posted Monday, October 01, 2012 4:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1366817
Posted Thursday, October 04, 2012 4:00 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 8:40 AM
Points: 51, Visits: 316
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%'

Post #1368285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse