Layer2 Data Provider for File System - Specifications

​The Layer2 Cloud Connector can be used to connect and sync pre-selected local documents including metadata on a Microsoft Windows file share to Microsoft SharePoint libraries as used in SharePoint Server on-premises, Office 365 / SharePoint Online, OneDrive for Business, Microsoft Teams, Office Groups, and others. It can also be used to sync just the metadata (not the files) to SharePoint Lists. The provider supports both read and write.

This page gives the full specifications of the Layer2 Data Provider for File System which is included in the Cloud Connector product distribution.

Layer2 Data Provider for File System: Connection String

As long as a complete select statement is used, the connection string can be left empty, otherwise it usually looks like this:

User

This part of the connection string specifies the username for the account which is used to authenticate. It needs to be specified for several authentication methods. See the Authentication Methods section for details.

Password

This defines the password for the account which is used to authenticate. It needs to be specified for several authentication methods by typing it into the Connection String or Password field. See the Authentication Methods section for details.

FilenameFilter

This is an optional search pattern for filtering files by name or extension. The parameter accepts standard windows file search patterns like these: * ? 
Note that only one filter can be set with this parameter.

FileReadMode

This is an optional setting for specifying how to read files. Options are: 
• Recursive (Default): Gets the content of all folder and subfolder recursively.
• Flat: Gets the files only in the specified folder excluding all subfolder contents.

FilesToInclude

This is an optional setting for reading hidden or protected system files. The parameter has three possible values: 
• Visible (Default): Hidden or protected system files will be excluded. 
• Hidden: Only protected system files will be excluded.
• All: All files regardless from its attribute values will be included.

IncludeFolders

This setting is optional. If it is not specified, the provider will read all folders as records for synchronization by default. If this is not applicable, for example to create a flat list of all files in the folder-hierarchy, it can be set to “false”.

IncludeDirectorySize

This setting is optional. If it is not specified, the provider will return 0 for all directory-sizes. This is due to optimization: To determine the size of a folder, it is necessary to traverse through the whole directory-tree and sum up the file-sizes. This can be very slow on a large number of files and is in many cases unnecessary. To retrieve the actual sizes, set this to “true”.

Layer2 Data Provider for File System: Data Query

​The data query retrieves the desired documents from the file system.

Select Statement  

The File System Provider supports a SQL-like syntax to filter the files and folders found in the given directory as described below.

SELECT [Fields] FROM [Directory] WHERE [Filter]

The list of [Fields] can either contain a wildcard (*) or a comma-separated list of field names. Furthermore, it is possible to rename the fields by using the SQL alias-syntax. For example,  SELECT FileExtension AS ext would select the contents of the file extension field and populate it as a field named ‘ext’ in the result.

 
The FROM part of the select statement currently supports exactly one directory path. This is optional if the connection string contains the ‘Directory’ parameter, otherwise it is mandatory. If both are given, the select statement has a higher priority and a warning will be logged.

The WHERE clause supports various elements known from SQL to build a complex conditional filter:

• Conditional operators: <, >, <=, >=, =, <>, LIKE, IN
• Logical operators: NOT, AND, OR
• Brackets can be used to change precedence

Various wildcards can be used with conditional operators. LIKE supports “%” (any characters) and all other conditional operators support “?” (exactly one character) and “*” (like %).

Example filter only includes files/folders in specific folders:
WHERE FilePath IN ('/myFolderA/*', '/myFolderC/myFolderD/*') 

Example filter excludes files/folders in specific folders:
WHERE NOT FilePath IN ('/myFolderA/*', '/myFolderC/myFolderD/*')

Example for a simple filter to get all txt files:
WHERE FileExtension = ‘.txt’

Example for a complex filter to get all files created before the 1st of February 2016 with ‘customer’ in their file names:
WHERE Filename = ‘*customer*’ AND Created < ‘2/1/2016’

Example filter selects all files starting with the letter ‘A’:
WHERE Filename LIKE ‘A%’

Example filter selects all files starting with the letter ‘A’ and ending with ‘CDE’: 
WHERE Filename = ‘A*CDE’

Example filter selects all files bigger than 1 GB:
WHERE Size > 1 GB

Example filter excludes all files with extension .aspx:
WHERE NOT FileExtension LIKE “.aspx”

Supported units for file-sizes are:
• KB = Kilobytes • MB = Megabytes • GB = Gigabytes

 
If the “IncludeFolders” parameter is “true”, which is the default, all folders that contain files matching the filter, will implicitly be included in the result, even if they do not match the filter. This is to make sure files will never be returned without the respecting folders. If only files should be returned, the “IncludeFolders” parameter needs to be set to “false”.

Layer2 Data Provider for File System: Primary Key(s)

No primary key must be given in Layer2 Cloud Connector. The complete file path is set automatically.

Layer2 Cloud Connector Sample Configurations

An example configuration of a file system data source in the Layer2 Cloud Connector can be found here.

Layer2 Data Provider for File System: Use Cases, Known Issues and Workarounds

Use cases, best-practices, known issues, and workarounds can be found here.

Explore frequently asked questions by topics.

Ready to go next steps?