November 2, 2016 at 7:55 am
Deny Christian (11/1/2016)
else,i used this syntax
DECLARE @CMD sysname
--create .bat:
/*.bat:
@echo off
forfiles /p "d:\backup\bck_denyc\combine" /m *.bak /c "cmd /c del /Q @file" /d -30 --to delete 30 days back
@echo off
*/
--execute the .bat:
exec @cmd = xp_CMDShell 'd:\backup\bck_Denyc\"delete folder".bat'
If (@CMD = 1)
PRINT 'SUCCESS'
ELSE
PRINT 'FAILURE'
That'll work, as well. 2 things, though... 1) You might not want to use @ECHO OFF because the output from xp_CmdShell could be important if an error occurs. You can easily capture the output in a Temp Table to check for errors. And, 2) speaking of errors, the Success/Failure test will only indicate if xp_CmdShell executed and successfully exited. It won't tell you if the underlying (in this case) DOS command successfully executed.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2016 at 10:21 pm
Jeff Moden (11/2/2016)
Deny Christian (11/1/2016)
else,i used this syntax
DECLARE @CMD sysname
--create .bat:
/*.bat:
@echo off
forfiles /p "d:\backup\bck_denyc\combine" /m *.bak /c "cmd /c del /Q @file" /d -30 --to delete 30 days back
@echo off
*/
--execute the .bat:
exec @cmd = xp_CMDShell 'd:\backup\bck_Denyc\"delete folder".bat'
If (@CMD = 1)
PRINT 'SUCCESS'
ELSE
PRINT 'FAILURE'
That'll work, as well. 2 things, though... 1) You might not want to use @ECHO OFF because the output from xp_CmdShell could be important if an error occurs. You can easily capture the output in a Temp Table to check for errors. And, 2) speaking of errors, the Success/Failure test will only indicate if xp_CmdShell executed and successfully exited. It won't tell you if the underlying (in this case) DOS command successfully executed.
Thank you Sir,
please advice if something is missing or needs to be added in its syntax ?
How to know if the DOS command successfully executed ?
November 3, 2016 at 7:08 am
Deny Christian (11/2/2016)
Jeff Moden (11/2/2016)
Deny Christian (11/1/2016)
else,i used this syntax
DECLARE @CMD sysname
--create .bat:
/*.bat:
@echo off
forfiles /p "d:\backup\bck_denyc\combine" /m *.bak /c "cmd /c del /Q @file" /d -30 --to delete 30 days back
@echo off
*/
--execute the .bat:
exec @cmd = xp_CMDShell 'd:\backup\bck_Denyc\"delete folder".bat'
If (@CMD = 1)
PRINT 'SUCCESS'
ELSE
PRINT 'FAILURE'
That'll work, as well. 2 things, though... 1) You might not want to use @ECHO OFF because the output from xp_CmdShell could be important if an error occurs. You can easily capture the output in a Temp Table to check for errors. And, 2) speaking of errors, the Success/Failure test will only indicate if xp_CmdShell executed and successfully exited. It won't tell you if the underlying (in this case) DOS command successfully executed.
Thank you Sir,
please advice if something is missing or needs to be added in its syntax ?
How to know if the DOS command successfully executed ?
Just capture the output of the xp_CmdShell command into a Temp Table using INSERT/EXEC and check what's in the Temp Table. It's one of the great advantages of using xp_CmdShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2016 at 7:47 am
Jeff Moden (10/6/2016)
rsampson (10/6/2016)
Using strictly ssis tasks, for each file enumerator, script task to get file info,conditionally delete file, done.
Works great if the directory is not empty, barfs a warning, then fails after 30 messages.
I'd like to not get the warnings, how to ?
I realize this thread is old but the post above necro'd it. 🙂
If you're trying to delete all of a particular file type (or all files) prior to a certain date, why not avoid having to write loops, write your own conditional tests, and etc, etc? Call a command task that uses FORFILES. You can use a single line of code to delete all files with a certain extension in the "current" directory and all sub-directories.
Here's an example of listing the "Modified Date" and the "FULL PATH" of all files with a mask of "*.txt" the live in the "C:\Temp" directory and all of its sub-directories (/S is for recursion of all sub directories) that have a "Modified Date" date that occurred prior to 01/01/2016 (mm/dd/yyyy - no other format is possible).
FORFILES /P "C:\Temp" /M *.txt /S /D -01/01/2016 /C "CMD /C ECHO @FDate @Path"Change everything to the right of "CMD /C" to the delete command that you want (probably DEL @Path) and you're done.
For more information, please see the following two links...
[font="Arial Black"]FORFILES command[/font]
[font="Arial Black"]DEL command[/font]
Personally, I avoid SSIS and PowerShell for things like this. I hate writing loops. Let the machine write its own pseudo-cursors for you, just like in T-SQL set based code.
As a bit of a sidebar, file deletions are mostly permanent and you can make some terrible mistakes. My recommendation would be to have one command to move the files to an "intended delete" area (RoboCopy works well as a separate command not using FORFILES) and only delete from that area after a given period of time. That will give people the opportunity to miss their files and bug you to put them back, which you'll easily be able to do because you will have them.
The thread may be old, but the technique is still simple and efficient. In short, it works well.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply