SET NOCOUNT ON
DECLARE @SQL VARCHAR(MAX)
DECLARE @TEMPSET TABLE (Val VARCHAR(4000))
INSERT @TEMPSET
(Val)
EXEC sys.xp_cmdshell
'set 0' ;
WITH s1
AS (SELECT DISTINCT
ConfiguredValue AS Directory
FROM Util.dbo.GlobalSSIS_Config
WHERE PackagePath LIKE '%tempdir%'
AND configuredValue LIKE '%\%'
UNION
SELECT DISTINCT
p.C2
FROM @TEMPSET
CROSS APPLY Util.dbo.ParseDelimitedColumns4(val, '=') p
WHERE val LIKE '%dirtemp=%\%'
UNION
SELECT DISTINCT
'C:\Apps\' + a.NAME + '\' + b.NAME + '\'
FROM Util.FS.GetDirectoryInfo('C:\apps\', '*.*') a
CROSS APPLY Util.FS.GetDirectoryInfo('C:\apps\' + a.NAME, '*.*') b
WHERE a.isdirectory = 1
AND b.isdirectory = 1
AND b.name = 'Logs')
SELECT @SQL = Util.dbo.StringConcat('PRINT ''forfiles /P ' + Directory + ' /m *.* /s /d -7 /c "cmd /c DEL @path /F /S /Q"''
EXEC xp_cmdshell ''forfiles /P ' + Directory + ' /m *.* /s /d -7 /c "cmd /c DEL @path /F /S /Q"''', '
')
FROM s1
EXEC Util.dbo.PrintLargeText
@SQL
PRINT 'GO'
IF @SQL <> ''
EXEC(@SQL )