SharePoint Online Large Scale External Data Integration (>5.000 Items)
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
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.
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
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.
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
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.