Filter msdb.dbo.sysjobsteps.command into seperate columns

I am trying to split the msdb.dbo.sysjobsteps.command column into separate columns to show the following information:

• folder
• project
• dtx package

A small sample of my data is below:

/ISSERVER ""SSISDBVWGroup_PackagesAudiME SSIS Continuous LoadDealerLoad_GX_FM_WW.dtsx""
/ISSERVER ""SSISDBVWGroup_PackagesVWG IN SSIS Continuous LoadABC to DW - Unapproved.dtsx""
/ISSERVER ""SSISDBMercedes_PackagesMercedes Cars SSIS Continuous LoadSubmissions Load.dtsx"" 
/ISSERVER ""SSISDBMercedes_PackagesMercedes Cars SSIS Continuous LoadValidations Load.dtsx"" 
/ISSERVER ""SSISDBAGCO_Packagesagco SSIS Continuous LoadMetis to ABC - agco UK.dtsx""
/ISSERVER ""SSISDBAGCO_Packagesagco SSIS Continuous LoadMetis to ABC - agco ie.dtsx"" 
/ISSERVER ""SSISDBVWGroup_PackagesVWG PL SSIS Continuous LoadABC to DW - Approved.dtsx"" 
/ISSERVER ""SSISDBYamaha PackagesYamaha SSIS Packages and WareHouse_LoadsABCtoDWLoadModifiedCnt.dtsx""
/ISSERVER ""SSISDBJLRJLR SSIS Continuous LoadSubmissions Load.dtsx"" 

I have tried to use the substring method, however I cannot seem to get the starting and ending number of the substring.

My aim is for the following in a ssms table:

Answer

You can use transform your data to xml to perform a “split” on backslashes and then you can extract only the data you need with an XQuery using the value() xml method (more info here on MS Docs).

In the following code I created a mock table called @tmp with your data:

declare @tmp table (package_path nvarchar(max))

insert into @tmp values
     ('/ISSERVER ""SSISDBVWGroup_PackagesAudiME SSIS Continuous LoadDealerLoad_GX_FM_WW.dtsx""')
    ,('/ISSERVER ""SSISDBVWGroup_PackagesVWG IN SSIS Continuous LoadABC to DW - Unapproved.dtsx""')
    ,('/ISSERVER ""SSISDBMercedes_PackagesMercedes Cars SSIS Continuous LoadSubmissions Load.dtsx""')
    ,('/ISSERVER ""SSISDBMercedes_PackagesMercedes Cars SSIS Continuous LoadValidations Load.dtsx""')
    ,('/ISSERVER ""SSISDBAGCO_Packagesagco SSIS Continuous LoadMetis to ABC - agco UK.dtsx""')
    ,('/ISSERVER ""SSISDBAGCO_Packagesagco SSIS Continuous LoadMetis to ABC - agco ie.dtsx""')
    ,('/ISSERVER ""SSISDBVWGroup_PackagesVWG PL SSIS Continuous LoadABC to DW - Approved.dtsx""')
    ,('/ISSERVER ""SSISDBYamaha PackagesYamaha SSIS Packages and WareHouse_LoadsABCtoDWLoadModifiedCnt.dtsx""')
    ,('/ISSERVER ""SSISDBJLRJLR SSIS Continuous LoadSubmissions Load.dtsx""')

;with splitted_packages
as (
    select 
        cast('<x>' + REPLACE(package_path, '', '</x><x>') + '</x>' as xml) as package_frament
    from @tmp
    )
select
     package_frament.value(N'/x[4]', 'nvarchar(max)') as Folder
    ,package_frament.value(N'/x[5]', 'nvarchar(max)') as Projects
    ,package_frament.value(N'/x[6]', 'nvarchar(max)') as Package
from splitted_packages

This is the final result of the previous command: