The folder data source within SmartConnect is a great way to automate an integration where the name of the file is not known at integration time or there are many files that need to be integrated throughout the workday. However, since the integration typically runs on a schedule, troubleshooting why some documents don’t process can be a bit tricky. This article will provide some methods for handling that process.

If this is the first time setting up a folder data source, I recommend reviewing the SmartConnect documentation on setting this up.

Symptom

The file(s) placed into the source folder does not get processed. If there are no other valid source files in the source folder, the map returns the message “No data was returned from the data source. Processing will stop.” This can happen when running the map manually or on a schedule although the reasons the file is not being processed could be different depending on the file location, user rights to that location and the file type.

File Names

The Filename does not matter as long it’s the full path to the file, whether it’s the source, success, failure and template location, doesn’t exceed the Microsoft Windows limitation of 260 characters. The file extension does matter and must the same as the template file extension. All file extensions must be lowercase. The name cannot contain multiple periods since SmartConnect finds the first period and assumes everything after that this is the file extension. If I have a text file named DataFile.txt.txt, it will not process when using a SmartConnect Folder Data Source.  The maximum file length of each text file name cannot be greater than 64 characters. Files with names longer than 64 characters long will get read as empty files.

Column Headings

If the file name is valid, the item validated by SmartConnect is the first row of the file, assuming it is a text file or Excel file, is the number of columns and the column names. Depending on the data source, Text File, Excel or XML, the results may vary as pointed out in the sections below.

At this time, the Folder Data Source is unable to process text files that do not contain column headings.

Text Files

Use the Create Schema File option when using File Type option of Text to tell SmartConnect what the expected layout of the source files will be. SmartConnect will assume every file has the layout matching this schema format. The Create Schema File option will look at the Template File for determining the column headings and data types.

This means that if the source files don’t match the format of the template or schema file, the results may not be as expected. The file may process but data may not be mapped as expected due to missing or additional columns. For example, if column 2 is missing in the source file, the data is shifted over a column and column 3 data now becomes column 2 data.

If the Create Schema File option is not used, the column names and number of columns for the Source files must match exactly as they do in the Template File with the only possible variation allowed is where there is a space in the column name. If the source file does meet these criteria, the file is not processed and remains in the Source Folder.

Excel Files

When using Excel Files, the number of columns and column headings must match exactly between the source file(s) and the template file. If they do not match, the file will remain in the Source folder and not be processed.

The Folder Data Source is only able to read from a single Worksheet which must exist in the Source File(s). Additional worksheets are allowed in the Excel Workbook since they are not used during the integration, however, formulas that reference those additional worksheets can be used on the main worksheet.

When the File Type is Excel2013/Excel2015/Excel2016, SmartConnect is unable to read an Excel File saved with the XLS (Excel 97) extension.

XML Files – XML

The setup for the XML File Type is the same as Text or Excel Files. There is an assumption the template file and the source file are valid XML files.

There are two options for processing XML files. The option shown here is more restrictive in how the data is processed. In other words, the source files must match the template exactly. If XML nodes are missing or extra nodes are present in the Source File but exist in the Template, the file will not be processed.

XML Files – XML Query

The setup for the XML Query File Type is the same as XML, Text or Excel Files. There is an assumption the template file and the source file are valid xml files.

There are two options for processing XML files. The option shown here is the XML Query. You won’t notice any difference in the map setup process, however, when executing the integration, this option will allow missing nodes from the source file, provided those nodes are not mapped to the destination.

If XML nodes are missing or extra nodes are present in the Source File but exist in the Template, the file will still be processed. Only the nodes that are mapped are considered required in the source file.

Preview

After setting up the folders the typical step is to click the Preview Button. For Folder Data Sources, this will only display the column headings that are available to map by looking at the Template File. If there is data in the template file, it will display that data. If there is no data, the preview window will be blank. Preview does not look to the Source folder and read the files in the folder.

If you do want to see the source data. Go to the destination mapping.

Click on the Preview Button. This shows data in the source file(s) grouped by the Key Fields that were set in the Map Source section.

Scheduling

When scheduling maps where the file are not placed on a local drive where the SmartConnect Windows Service is running, UNC paths must be used.

To set the UNC Path for the template file, click on the Template File lookup. Browse the server, select the file or enter the UNC Path in the File Name field, and click Open.

Setting the Source, Success and Failure folders are the same process as setting the template file. However, you only enter the folder path.

Click the Lookup button to the right of each folder to open the folder lookup window.

The Domain user assigned to the SmartConnect Windows Service must have Read/Write access to all the folders and files used with a Folder Data Source integration. You can validate which user needs those rights by going to the machine(s) where the SmartConnect Windows Service is installed, going to Administrative Tools->Services and looking for the eOne SmartConnect Service.

Event Logging

With SmartConnect 20.17, an event will be written to the SmartConnect Event Log listing every file that cannot be processed and possibly why it cannot be processed.

Navigate to the Event Logs window by going to the Logs menu and click on Event Logs. This window will display all errors logged against this SmartConnect instance to the SmartConnect Windows Event Log.

Another example of an error that may be received.

Real-Time Option

In this document, the assumption is the Real-Time Folder option is not being used. Although, if it is, there are some additional requirements that need to be reviewed when checking the items below. If these items are not set, the real-time folder will not occur and no documents will be processed.

Items to Review

This is a short list of the items to review on why a file may not be processed or found.

  • A user has the Template File open when the integration runs
  • The Source Files are not in the same format as the Template File
  • The Template File layout hasn’t changed
  • The user or scheduling user does not have access to the folders due to windows security
If you have any more questions, feel free to email us at sales@eonesolutions.com