SharePoint Online Large Scale External Data Integration (>5.000 Items)

​The Layer2 Cloud Connector for Office 365 and SharePoint can be used to connect almost any external data source to native SharePoint lists, e.g. SQL, ERP/CRM, XML, ODBC, OLEDB, OData etc. It is made for large scale external data integration scenarios as well and can manage more than 5.000 items without any issues. It helps to overcome the SharePoint limitations with out-of-the-box data integration via BCS using data synchronization.

SharePoint out-of-the-box data integration tools like BCS come with a limitation of 2.000 items per default. It can be increased, but it is definitely not made for 5.000 items and more large scale integration. On the other hand, SharePoint lists can host up to 30,000,000 items with a view limit of 5,000 (list view throttling). You can solve the issue by using the Layer2 Cloud Connector Connector for data synchronization.

Lets take a look at a data integration sample with 45.575 items from Microsoft Excel, e.g. coming from a LOB system export. It can be synchronized in just a minute...

Fig.: Sample Excel data source of Airports with 45.575 records.

The first synchronization can take time, depending on configuration and bandwidth up to some hours. You can expect not more than 1-2 inserts / updates per second. If finished once it should look like this:

Fig.: The SharePoint Online list with synchronized 45.575 Airports from Excel. The Airport name was mapped to the column Title in SharePoint to provide a consistent UI.

The list settings shows the following warning (in your language):

Fig.: The SharePoint Online List Threshold of 5.000 was exceeded.

Exceeding the list view threshold means that some operations (like some views with sorting, grouping or filter operations) cannot be executed with this list anymore. But you can continue to use the unmodified Allitems view (without sorting and filters). Indexing columns can help in some cases to overcome the issues with views. You can use search or managed metadata to present specific results to your users with very high performance.

You can continue to automatically update the list with data changes via the Layer2 Cloud Connector for SharePoint in background via scheduling.

Fig.: Synchronization of a SharePoint list of 45.576 items automatically (with one item update required) takes around 1 minute only.

Please note:

• You can integrate SharePoint Online lists with external data sources with more than 5.000 items (list view throttling limit) without any issues by data synchronization via the Layer2 Cloud Connector.
• Use an unsorted unfiltered view as full URL in connection string or completely remove all view information in connection string using the url=mySite; list=myList notation. Please note: In some configurations the AllItems view is sorted by name by default. That will not work above 5.000 items if used in connection string.
• Sync as few columns as possible.
• Provide high bandwidth for sync.
• You users will have full SharePoint performance and features, because they are working with the native SharePoint list instead of pulling all data via BCS on demand.
• No user CAL or other license is required for the external system because SharePoint users are working with a "data copy" of the external data in SharePoint.
• Users can continue to work in case the external source is not available for any reason.
• Change notifications and workflows can be used to keep users informed about external data changes.
• SharePoint-only columns can be added and are kept during data updates.
• SharePoint search can be used to present specific subsets.
• Generally the connection can be bi-directional as well, but the data provider must support this operation and access rights must be given to write to the data source.

Layer2 Cloud Connector - Next Steps

You can download a free trial of the Layer2 Cloud Connector after registration here.

Ready to go next steps?