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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top