One common requirement in SQL Server is to import the latest file in a folder and append it to a table for processing. This can be achieved using various methods, and in this article, we will explore one approach using an ActiveX task and a Dynamic Properties task.

Setup

First, we need to set up three global variables in the package that will be used in the ActiveX task. These variables are:

• FileFolder: Holds the UNC path to the folder where the file you wish to process will reside.
• FilePrefix: The string the file always starts with, enabling you to choose a subset of files from a common area.
• FileName: The variable that will hold the name of the latest file.

Next, add a connection of type Text File (Source) and choose a valid file name at this point, which we will reset later. Choose the appropriate format as you would normally do.

Finding the Latest File

Now, let’s add an ActiveX task to find the name of the latest file. In the ActiveX task, add the following code:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
    Dim filePreFix, folderName, fileName
    folderName = DTSGlobalVariables("FileFolder").Value
    filePreFix = DTSGlobalVariables("FilePrefix").Value
    'Call the function, passing in the two Global Variables obtained above.
    fileName = LatestFile(folderName, filePreFix)
    DTSGlobalVariables("FileName").Value = fileName
    If DTSGlobalVariables("FileName").Value <>  "" Then
        Main = DTSTaskExecResult_Success
    Else
        Main = DTSTaskExecResult_Failure
    End If
End Function

'----------------------------------------------------------------------
Private Function LatestFile(MyFolderName, MyFilePrefix)
'----------------------------------------------------------------------
On Error Resume Next
    Dim oFSO, HighestDate, MyResultName, myFolder, file
Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set myFolder = oFSO.GetFolder(MyFolderName)
    MyResultName = ""
    HighestDate = DateValue("1/1/1995")
    For Each file In myFolder.files
        'Check to make sure the file starts with the right stuff
        If UCase(Left(file.name,Len(MyFilePrefix))) = UCase(MyFilePrefix) Then
            'Check last modified date
            If file.DateLastModified > HighestDate Then
                MyResultName =  file.path
                HighestDate = file.DateLastModified
            End If
        End If
    Next
    LatestFile = MyResultName
    Set myFolder = Nothing
    Set oFSO = Nothing
End Function

This code uses the FileSystemObject to iterate through the files in the specified folder and find the latest file based on the file prefix and last modified date. The name of the latest file is then stored in the FileName global variable.

Handling No Files Found

In case there are no files to process, we can add an ActiveX task to log that there was nothing to do. This is useful for checking if the package actually ran. The code for this task is:

'**********************************************************************
'  Visual Basic ActiveX Script
'**********************************************************************
Function Main()
LogAction "C:\Logs\", "FileImport.Log", "No files found to process!"
Main = DTSTaskExecResult_Success
End Function

'**********************************************************************
Sub LogAction (folder, filename, strEntry)
'**********************************************************************
Dim strErrMsg, f, LogFile, oFS
Set oFS = CreateObject("Scripting.FileSystemObject")
MakeSureDirectoryTreeExists(folder)
LogFile = folder & filename
Const ForReading = 1, ForWriting = 2, ForAppending = 8
On Error Resume Next
If Not (oFS.FileExists(LogFile)) Then
oFS.CreateTextFile(LogFile)
End If
set f = oFS.OpenTextFile(LogFile, ForAppending)
f.WriteLine "[ " & Now & " ] - " & strEntry
f.close
On Error Goto 0
set f = Nothing
Set oFS = Nothing
End Sub

'**********************************************************************
Function MakeSureDirectoryTreeExists(dirName)
'**********************************************************************
'like it says on the tin
Dim oFS, aFolders, newFolder, i
Set oFS = CreateObject("Scripting.FileSystemObject")
' Check the folder's existence
If Not oFS.FolderExists(dirName) Then
' Split the various components of the folder's name
aFolders = split(dirName, "\")
' Get the root of the drive
newFolder = oFS.BuildPath(aFolders(0), "\")
' Scan the various folder and create them
For i = 1 To UBound(aFolders)
newFolder = oFS.BuildPath(newFolder, aFolders(i))
If Not oFS.FolderExists(newFolder) Then
oFS.CreateFolder newFolder
End If
Next
End If
Set oFS = Nothing
End Function

This code logs an error message to a specified log file if no files are found to process. It uses the FileSystemObject to create the log file if it doesn’t exist and appends the error message with a timestamp.

Setting Up the Dynamic Properties Task

Now, let’s add a Dynamic Properties task to set the source connection to the latest file recovered in the first ActiveX step. To set this up, follow these steps:

1. Press “Add…” in the Dynamic Properties task.
2. Navigate to the Connection 1 Datasource, highlight it, and press “Set…”.
3. Choose “Global Variable (GV)” for the Source and “FileName” as the Variable. Note that it will be blank at this point.

Now, the Dynamic Properties task should reflect the latest file name in Connection 1.

Processing the File

Next, add a Database Connection, Connection 2, so we can add a Data Pump task between Connection 1 and Connection 2. At this point, Connection 1 should still be pointing to a valid file, so you can set the source, destination, and transformations as you would normally do. You may also add an optional SQL task to perform any further processing.

Testing the Package

Let’s review what we should have now:

• If we execute the Dynamic Properties task, we should see that the property of Connection 1 gets set to an empty string because the FileName global variable is currently empty.
• If we execute the first ActiveX task, we should see that the FileName global variable is set to the name of the latest file in the specified folder.
• If we execute the Dynamic Properties task again, we should see that the properties of Connection 1 now reflect the latest file name.
• If we temporarily move all files from the specified folder and run the entire package, it should fail and the workflow process will take us to the second ActiveX step, which will write out an error log in the specified location.
• To reset the FileName global variable back to blank, run the Dynamic Properties task again and save the package.
• Now, move a selection of files back to the specified folder and execute the package end-to-end. The first step should get the latest file name, the second step should set Connection 1 to it, and the Data Pump task should pull the data across. If you added the optional SQL task, it should also fire at the correct time.

Remember to reset the FileName global variable back to blank and re-run the Dynamic Properties task before saving your final version.

And there you have it! You have successfully implemented a solution to import the latest file in a folder in SQL Server using an ActiveX task and a Dynamic Properties task.

Properly implementing error handling, as shown in this example, can help ensure the smooth execution of your packages and provide valuable information in case of any issues.

Thank you for reading!