Data Sources Query
<p>Good Day, 
     I need to create a spreadsheet containing the information pertaining to my SSAS jobs. One piece of data that I need to include is the data sources used in the SSIS packages run in the jobs. I have a query but it returns an error message 'Invalid length parameter passed to the LEFT or SUBSTRING function.' Can anyone help me fix this or suggest a method that would allow me to access that information with more ease?</p>

<pre><code>USE msdb
GO

WITH XMLNAMESPACES (N'www.microsoft.com/SqlServer/Dts' AS DTS)

   SELECT JobName, PackageName, 
    pd.ConnectionManagerName,
    pd.ConnectionType,
    pd.ConnectionString     
FROM        
(SELECT
  JOB.name AS JobName, REVERSE(LEFT(STEP.ReversePackagePath, CHARINDEX('\', STEP.ReversePackagePath, 1) - 1)) AS PackageName            
FROM msdb.dbo.sysjobs JOB       
CROSS APPLY (
                 SELECT
                    REVERSE(LEFT(command, CHARINDEX('.dtsx', command, 1) - 1)) ReversePackagePath
 FROM  msdb.dbo.sysjobsteps                    
 WHERE
                    job_id = JOB.job_id
                    AND subsystem = 'SSIS'

 ) STEP
)  JobList
INNER JOIN [msdb]..[sysssispackages] p ON p.name = JobList.PackageName
CROSS APPLY (   
                SELECT
                    g.ConnectionManagerName,
                    g.ConnectionType,
                    g.ConnectionString
                    FROM
                    (   
                        SELECT
                            f.PackageXml,
                                                      CAST(CAST(f.PackageXml.query(N'/DTS:Executable[1]/DTS:Property[@DTS:Name="PackageFormatVersion"]/text()') AS nvarchar(25)) AS int) AS PackageXmlVersion
FROM (SELECT CAST(CAST(p.packagedata AS varbinary(MAX)) AS xml) AS PackageXml) f
                    ) px
                    CROSS APPLY
                    (
                        SELECT

CAST(x.query(N'./DTS:Property[@DTS:Name="ObjectName"]/text()') AS nvarchar(256)) AS ConnectionManagerName,
CAST(x.query(N'./DTS:Property[@DTS:Name="CreationName"]/text()') AS nvarchar(512)) AS ConnectionType,               
CAST(x.query(N'./DTS:ObjectData[1]/DTS:ConnectionManager[1]/DTS:Property[@DTS:Name="ConnectionString"]/text()') AS nvarchar(2048)) AS ConnectionString
FROM px.PackageXml.nodes(N'/DTS:Executable[1]/DTS:ConnectionManager') cm(x)
                        WHERE px.PackageXmlVersion = 3 /* 2008 R2 */
                        UNION ALL
SELECT
                            x.value(N'@DTS:ObjectName', 'nvarchar(256)'),
                            x.value(N'@DTS:CreationName', 'nvarchar(512)'),
                            (

SELECT
                                    y.value(N'@DTS:ConnectionString', 'nvarchar(2048)')


FROM x.nodes(N'./DTS:ObjectData[1]/DTS:ConnectionManager[1]') cp(y)
                            )

FROM px.PackageXml.nodes(N'/DTS:Executable[1]/DTS:ConnectionManagers[1]/DTS:ConnectionManager') cm(x)
                        WHERE px.PackageXmlVersion = 6 /* 2012 */
                ) g
        ) pd

 WHERE
        (p.packageformat = 0) AND /* XML format */
        (p.isencrypted = 0) AND
        (p.folderid = '00000000-0000-0000-0000-000000000000'); 
</code></pre>