If anyone can help me solve this very challenging, yet very solvable problem below, I would be so grateful! I have put a lot of work and effort to get to the point that I am at and I am so close to having a solution.

The Required Background Information

I have created a database named [PackageInventory]. I fill this database with SSIS Package information gathered from SQL Server 2008 R2 Packages and SQL Server 2012 Packages. Each row in the table is a different package.

The columns I collect, or gather are as follows:

[PackageInventory_ID]

      ,[ServerName]

      ,[PackageName]

      ,[PackageID]

      ,[PackagePath]

      ,[CreatorName]

      ,[ProtectionLevel]

      ,[PackageFormatVersion]

      ,[PackageType]

      ,[PackageDescription]

      ,[VersionMajor]

      ,[VersionMinor]

      ,[VersionBuild]

      ,[VersionGUID]

      ,[NumberOfTasks]

      ,[PackageHasIssues_Flag]

      ,[NumberOfIssues]

      ,[CurrentPackageXML]

      ,[PackageIssuesXML]

      ,[FixedPackageXML]

      ,[LoadDate]

The column [CurrentPackageXML] (xml), is a SQL Server XML Data Type. So, in other words, I am storing the raw XML for a SSIS package in a column. For this example I have included the Sample SSIS Package File XML Code below that relates to a row in the table above for a package named Test_Package_To_Find_SSIS_Objects_In_XML_Code. If you insert the provided Sample SSIS Package File XML Code below into a database with a column of the XML data type and create two columns named [PackageInventory_ID] (int) and[PackageHasIssues_Flag] (bit), you should be able to help me write a query to do the following task:

Desired T-SQL Query to Accomplish the Following Task:

How can I use T-SQL to inspect ALL of many <DTS:ConnectionManager> (the inner most tag bolded in the XML Sample Hierarchy of Any SSIS Package Connection Manager Below) tags for a DTS:ConnectionString property with an Initial Catalog = COMSOL_DW. If an Initial Catalog = COMSOL_DW, then update the column [PackageHasIssues_Flag] = 1 for the same row where the XML code was inspected.

      XML Sample Hierarchy of Any SSIS Package Connection Manager:

<DTS:ConnectionManagers>

<DTS:ConnectionManager>

<DTS:ObjectData>

<DTS:ConnectionManager DTS:ConnectionString = {CS}

** Where {CS} = Data Source=DCORECSI3DB01;Initial Catalog=COMSOL_DW;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

I prefer to use the XML Data Type Methods: Query(), Value(), Exist(), Modify, and Nodes.

If necessary, I am familiar with using statements like the one below to get the value stored in a SSIS XML Tag Property.

CurrentPackageXML.value('declare namespace DTS=www.microsoft.com/SqlServer/Dts; DTS:Executable[1]/@DTS:CreatorName[1]','nvarchar(500)'

I want to avoid casting the XML Data Type into a VarChar(max) and doing string manipulations!!!

Please Help

Download Sample SSIS Package File XML Code from URL Below:

http://www.aimreportwriting.com/samplexml/SampleSSISXML.xml