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)?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: