Q

How to find and delete file names

SQL Server Development expert Joe Toscano suggests working with the FileSystemObject and ActiveX Script Tasks when listing file names in .bak and .trn extensions. View his helpful sample codes.

I am trying to get a list of file names with .bak and .trn extensions from a specific path (say D: Test folder), which contains files. I want to search and delete the file names with .bak or .trn extensions with old dates. How is this done in T-SQL or Data Transformation Services (DTS)?
You may consider working with files and the FileSystemObject and ActiveX Script Tasks. Here's some sample code:

' Sample code that Copies A File
Option Explicit

Function Main()
 Dim oFSO
 Dim sSourceFile
 Dim sDestinationFile
 Set oFSO = CreateObject("Scripting.FileSystemObject")
 sSourceFile = "C:SourceFile.txt"
 sDestinationFile = "C:DestinationFile.txt"
 oFSO.CopyFile sSourceFile, sDestinationFile
 ' Clean Up
 Set oFSO = Nothing
 Main = DTSTaskExecResult_Success
End Function


' Sample code that Moves a File 
Option Explicit

Function Main()
 Dim oFSO
 Dim sSourceFile
 Dim sDestinationFile
 Set oFSO = CreateObject("Scripting.FileSystemObject")
 sSourceFile = "C:SourceFile.txt"
 sDestinationFile = "C:FolderDestinationFile.txt"
 oFSO.MoveFile sSourceFile, sDestinationFile
 ' Clean Up
 Set oFSO = Nothing
 Main = DTSTaskExecResult_Success
End Function


Here's one that may help you. Check the File Date & Time
Option Explicit


Function Main()
 Dim oFSO
 Dim oFile
 Dim sSourceFile
 Set oFSO = CreateObject("Scripting.FileSystemObject")
 sSourceFile = DTSGlobalVariables("SourceFileName").Value
 Set oFile = oFSO.GetFile(sSourceFile)
 If oFile.DateCreated < Date Then
  Main = DTSTaskExecResult_Success
 Else
  Main = DTSTaskExecResult_Failure
 End If
 ' Clean Up
 Set oFile = Nothing
 Set oFSO = Nothing
End Function
This was first published in June 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close