db042190 avatar image
db042190 asked

want to shred ssis conn managers i am extracting from msdb

Hi we run 2014 std and are researching a potential issue with an alias in one of our ssis environments.

one of my peers believes we have two types of packages on msdb in that environment. Some that currently run with two specific conn mgrs (one is sql the other ado) referencing alias "abc". And some that wont work with that alias unless those 2 specific conn mgrs are hard coded to the server on which that ssis environment runs.

there are often 5 conn managers in our pkgs. because "abc" is referenced in our pkgs for another reason , i think i need to learn how to shred two specific conn mgrs to find if either one refers to "abc". i tried using LIKE in my query to match on what shows after their guids but couldn't get that to work.

so in the example shown below , i show my base query followed by the typical conn mgrs anonymzed. The goal is to find examples where the 2nd and 3rd conn mgrs have the string "abc" between their beginning and ending tags. i will cast the varchar(max) to xml if necessary.

select * from 
select * , cast(cast(packagedata as varbinary(max)) as varchar(max))pd2
from sysssispackages 
) x

<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.app1]" DTS:CreationName="OLEDB" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.app1">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=aliasthatsok;Initial Catalog=app11;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;"/>        </DTS:ObjectData>      </DTS:ConnectionManager>      
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.app2]" DTS:CreationName="OLEDB" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.app2">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=hardcodedserver;Initial Catalog=app2;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;"/>        </DTS:ObjectData>      </DTS:ConnectionManager>      
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.app2 ADO.NET]" DTS:CreationName="ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=, Culture=neutral, PublicKeyToken=somekey" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.app2 ADO.NET">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=hardcodedserver;Initial Catalog=app2;Integrated Security=True;"/>        </DTS:ObjectData>      </DTS:ConnectionManager>      
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.SSISConfig]" DTS:CreationName="OLEDB" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.SSISConfig">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=abc;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"/>        </DTS:ObjectData>      </DTS:ConnectionManager> 
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[PackageLogFile]" DTS:CreationName="FILE" DTS:Description="Package Log File. Location is set dynamically using an expression." DTS:DTSID="{a guid}" DTS:ObjectName="PackageLogFile">        <DTS:PropertyExpression DTS:Name="ConnectionString">@[Template::LogFileName]</DTS:PropertyExpression>        <DTS:ObjectData>          <DTS:ConnectionManager DTS:FileUsageType="1" DTS:ConnectionString="some letter:\temp\LogFiles\pkgname_1.0.206_20201117131352.log"/>        </DTS:ObjectData>      </DTS:ConnectionManager>    
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

And for that specific example given, what do you want as output?

0 Likes 0 ·

good question. minimally connector object name (eg xxx.app2) and package name. i can make that a cte and join on variations of the base query.

0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

Try this:

with xmlnamespaces('' as DTS)
    pd_XML.value('(/DTS:Executable/@DTS:ObjectName)[1]','varchar(max)') as PackageName,
    managers1.value('(@DTS:ObjectName)[1]','varchar(max)') as ConnectionManagerName,
    managers2.value('(@DTS:ConnectionString)[1]','varchar(max)') ConnectionString
    (select cast(cast(packagedata as varbinary(max)) as xml) from sysssispackages ) packagedata(pd_XML)
cross apply pd_XML.nodes('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') AS connection1(managers1)
cross apply managers1.nodes('DTS:ObjectData/DTS:ConnectionManager') AS connection2(managers2)
    managers2.value('(@DTS:ConnectionString)[1]','varchar(max)') like '%abc%'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

db042190 avatar image
db042190 answered

thank you. if it isn't against this forums rules, can you tell me (or point me at an artcle) what sql feature (pd_XML) represents in the following statement? I'm going to try to understand this, not just use it. I assume packagedata just before it is just a table alias or name like we always use for sub selects.

(select cast(cast(packagedata as varbinary(max)) as xml) from sysssispackages ) packagedata(pd_XML)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's simply another way of aliasing the columns, it could have been written like:
(select cast(cast(packagedata as varbinary(max)) as xml) as pd_XML from sysssispackages ) packagedata

0 Likes 0 ·

appreciated Kev. makes sense.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.