SSIS physical path

问题:

I have a problem with finding the physical path of the SSIS package because I handover someones job and the package name in SSIS is different than in the disc. There are a lot of SSISes and it is hard to find the correct one. I found the solution which displays all packages bu there is no information about physical path. Please halp.

with ChildFolders
as
(
    select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
        cast('' as sysname) as RootFolder,
        cast(PARENT.foldername as varchar(max)) as FullPath,
        0 as Lvl
    from msdb.dbo.sysssispackagefolders PARENT
    where PARENT.parentfolderid is null
    UNION ALL
    select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
        case ChildFolders.Lvl
            when 0 then CHILD.foldername
            else ChildFolders.RootFolder
        end as RootFolder,
        cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
            as FullPath,
        ChildFolders.Lvl + 1 as Lvl
    from msdb.dbo.sysssispackagefolders CHILD
        inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
)
select F.RootFolder, F.FullPath, P.name as PackageName,
    P.description as PackageDescription, P.packageformat, P.packagetype,
    P.vermajor, P.verminor, P.verbuild, P.vercomments,
    cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData
from ChildFolders F
    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
order by F.FullPath asc, P.name asc;

回答1:

My SSIS Package Query is similar to yours. The difference between your path and mine is that I have a backslash for pathing whereas you use a forward slash.

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT 
    F.FolderPath
,   P.PackageName
,   F.FolderPath + '\' + P.PackageName AS PackagePath
,   P.packageFormat
,   P.packageType
,   P.vermajor
,   P.verminor
,   P.verbuild
,   P.ownername
,   P.PackageId
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid;

Results (for a default 2008 box).

FolderPath  PackageName PackagePath
\Data Collector PerfCountersCollect \Data Collector\PerfCountersCollect
\Data Collector PerfCountersUpload  \Data Collector\PerfCountersUpload
\Data Collector QueryActivityCollect    \Data Collector\QueryActivityCollect
\Data Collector QueryActivityUpload \Data Collector\QueryActivityUpload
\Data Collector SqlTraceCollect \Data Collector\SqlTraceCollect
\Data Collector SqlTraceUpload  \Data Collector\SqlTraceUpload
\Data Collector TSQLQueryCollect    \Data Collector\TSQLQueryCollect
\Data Collector TSQLQueryUpload \Data Collector\TSQLQueryUpload

回答2:

I found a workaround to that. I just creatednew SSIS project in Visual Studio and there is an option to add existing SSIS package:

Then I just logged to a server where packages are stored:

and navigated to a package I was looking for.

回答3:

Use the folders_cte common table expression below. Then join folders_cte to the msdb.dbo.sysssispackages table on the folderid column. From there, adding additional columns to the SELECT clause at the very bottom is relatively easy, but for simplicity I've only included three.

;WITH folders_cte AS 
(
    SELECT 
        p.folderid,
        p.parentfolderid,
        p.foldername,
        CAST(p.foldername AS VARCHAR(8000)) [path]
    FROM msdb.dbo.sysssispackagefolders p
    WHERE folderid = '00000000-0000-0000-0000-000000000000' 
    UNION ALL 
    SELECT 
        c.folderid,
        c.parentfolderid,
        c.foldername,
        CAST(folders_cte.path+c.[foldername] + '\' AS VARCHAR(8000)) [path]
    FROM msdb.dbo.sysssispackagefolders c
    INNER JOIN folders_cte on
    c.parentfolderid = folders_cte.folderid
)
SELECT f.path Path, p.name PackageName, f.path + p.name AS FullPath
FROM msdb.dbo.sysssispackages p
INNER JOIN folders_cte f ON 
p.folderid = f.folderid