Pre-select local documents for SharePoint Online file synchronization using SQL

The migration, synchronization, or backup of pre-selected files on local department’s network file shares or user’s home drives is often one of the first steps when companies or organizations are moving to the Microsoft Cloud. However, Microsoft’s free out-of-the-box tool, the OneDrive for Business sync client, is known to have issues and limitations regarding file selection, file name conventions, file types allowed, number of documents supported for sync, and platform availability. The Layer2 Cloud Connector can help to close these gaps with flexible, SQL-style pre-selection queries of local document sets for synchronization.

Customers typically have terabytes of data on their file servers. Typically, they want to make some specific pre-selected document sets available for collaboration and mobile access in the Microsoft Cloud (SharePoint Online or OneDrive for Business) – by one-time migration, for permanent two-way synchronization, or for backup.

The Layer2 Cloud Connector since version 6.2 offers the flexibility they need to aggregate documents from different sources into one SharePoint library or library folder, or split sources to several libraries using flexible SQL-like queries. Administrators can centrally setup and manage the document synchronization. They can include or exclude documents from the synchronization process using very flexible SQL-like filters for folder or file names, file types, size and date conditions, and more.

Setup SQL-like queries to pre-select files for SharePoint file synchronization

You can centrally select your documents for Microsoft cloud data synchronization in the Connection Manager as follows.

Fig.: Query sample to only sync files with specific names and exclude very large files.

SQL query samples of file server document selections for SharePoint sync

The Layer2 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 %).

Include only specific folders and subfolders into sync:

WHERE FilePath IN ('/myFolderA/*', '/myFolderC/*')

Exclude specific folders from sync:

WHERE NOT FilePath IN ('/myFolderB/*')

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 filenames:

WHERE Filename = ‘*customer*’ AND Created < ‘2/1/2016’

Example filter selects all files starting with the letter ‘A’ (uppercase / lowercase ignored):

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

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.

FAQs - File Server document selection for sync to SharePoint Online via SQL

​Which file data sources are supported for SQL-like queries?

Supported are folders of local disks, network shares (e.g. for departments \\myServer\marketing), user's home drives and similar).

Got something like "An error occurred while executing connection 'myConnection': Error reading from entity 'mySource': Cannot find column [‘.docx’]." How to fix this?

Please be sure to use standard quotes for tokens like '.docx'. In some cases special chars (from copy / paste) can cause such issues. Simple re-typing can fix.

Are other file sharing vendors supported like Google Drive, myBox etc.?

They are not supported for direct access, but no problem to sync e.g. Google Drive with a local share, and sync to SharePoint Online from there.

Can I centrally sync files on my client PCs, tablets and other mobile devices?

Yes, you can use administrative shares to connect and sync e.g. from \\192.168.111.161\c$\myfiles.

Where do I get more information?

Please take a look at the User's Guide (part of the distribution) or contact sales@layer2solutions.com for answers.

Next Steps with SharePoint Online & OneDrive file synchronization

You will find more about file synchronization using the Layer2 Cloud Connector here. You can register for download and evaluation of the Layer2 Cloud Connector today.

READY TO GO NEXT STEPS?