Tech Tuesday: Using a Script to Replicate a Folder Data Source

A request that we have seen occasionally from different clients is that they want the flexibility of a folder data source, but the functionality of an ODBC connection when using a flat file as a data source. We’ve written a script that can be applied to any flat file (Excel, text, XML, etc.) which will allow a map to read data from a file no matter what it’s named, while allowing a standard ODBC connection to be used in the map setup process.

If you aren’t familiar with a folder data source or an ODBC data source, here is a quick explanation of each data source type and its benefits. A folder data source allows a client to specify an entire folder as a source, rather than an individual file. This allows multiple files to be read in at once to SmartConnect, and those files will be read in no matter what the file names are. The drawback is that all the data will be pulled from each file, you cannot setup a filter or query to return only specific rows from each file. Meanwhile, an ODBC connection allows filtering of the data when connecting to the specific file. WHERE clauses and other restrictions can be used in the source. One of the drawbacks of this method is that the source file needs to have a specific name, otherwise the map will not recognize it.

The script that is included in this article is meant to be setup at the pre-map task level. The script will take the first file in a specified folder and rename and/or move it to another folder. The idea behind it is that you can use this script to rename different files to one standard name, and then have the map execute and process using an ODBC connection to that specific file. It combines many of the benefits from each data source type into one process. The only drawback is that it will read in one file at a time, but the map can be scheduled more frequently if there are multiple files that need to be processed by the map in a short period of time.

An example where this could be used is in the screenshot below. We are receiving an export file of customers that need to be imported into our system each day. The files are timestamped as part of the filename. Due to the data in the files, we don’t want to use a folder data source as we need to filter the information down further.

We will setup the map to read data from a file named “GP_Customers”. We will then setup the following script as a pre-map task to ensure that we take the first file and rename it to GP_Customers and move it to the correct folder before processing.

‘Declare the original folder the files are located in 
Dim fileFolder As String = “C:SmartConnect DataDemoDynamics GPData” 

‘Declare the folder that the map is pointed at, along with the desired file name 
Dim newFileFolder As String = “C:SmartConnect DataDemoDynamics GPMap Source” 
Dim newFileName As String = “GP_Customers.txt” 

‘Get the directory info from the info provided 
Dim fileName As String 
Dim dirInfo As New IO.DirectoryInfo(fileFolder) 
Dim files = dirInfo.EnumerateFiles() 

‘Grab the first file from the directory 
For Each f as FileInfo in files 
fileName = f.Name 
Exit For 

Next 

‘Delete the file from the destination location if a file is already there 
If File.Exists(newFileFolder & newFileName) Then 
File.Delete(newFileFolder & newFileName)  
End If 

‘Move the file 
File.Move(fileFolder & fileName, newFileFolder & newFileName) 

return true 

The sections that would need to be modified are the first couple lines where the fileFolder, newFileFolder, and newFileName variables are declared. fileFolder should be set to the folder where the files are originally being creating. newFileFolder should be set to the directory that SmartConnect is looking at for the source (it could be the same as the original folder). Finally, newFileName should be the name of the file that SmartConnect is expecting in the ODBC query.

This script should be set on the pre-map task section of the map and will automatically move one file per map run to the desired data source folder. You will want to setup a task to move the file out of the folder on completion of the map as this script is setup to delete the file from the destination folder it is copying the file to if it already finds one there with the same name.

The script itself is commented throughout and can be edited as needed to fit whatever scenario might require some additional configuration.