SharePoint developers and power users have had a love-hate relationship with SharePoint’s BCS and its’ older brother BDC (Business Data Catalog). BCS requires several services to be installed along with some user accounts and permissions. If any of those services become unresponsive or go offline, BCS becomes effectively useless along with any SharePoint lists that utilize the related External Content Types. In other words, if the external data source is down or any of the BCS dependencies fail; you will not be able to create or update records that are connected to External Content Types.
Fig 1. Business Connectivity Services and SQL Server
As an alternate solution, use SQL Server Integration Services to push data from SQL (or other SSIS supported data sources) to SharePoint lists. Once in SharePoint, users can work with the data just like any other SharePoint list data. SSIS can be configured to execute nightly, hourly, or every minute to keep the data in SharePoint up to date. SSIS can also be used to extract data from lists and libraries and push the data to SQL server or even data warehouses for reporting and analytics. Using these two import/export mechanisms, it is possible to build a package that can synchronize data between SQL and SharePoint (i.e. users can update records in SharePoint).
Fig 2. SQL Server, SSIS, and SharePoint as a BCS alternative
Deciding which path to take will require careful thought and planning, so let’s compare some of the benefits and tradeoffs. BCS can be tricky to setup, but can be done entirely by your SharePoint admin. SSIS on the other hand will require a DBA or developer to implement. SSIS does have a large selection of integration points to work with: Most flavors of SQL Server, SAP, Salesforce, Excel, and web services. There is also a healthy community of data connectors available for free and purchase by various vendors. BCS focuses primarily on SQL Server, OData, and WCF; you can develop your own custom connectors if needed using .NET. On the topic of custom components, not all third party apps, features, or services support BCS columns, but do support the standard SharePoint Lists or Lookup column. This can be an important consideration if your SharePoint Farm has a third party workflow engine, scanning solution, or is saturated with Apps from the SharePoint Store.
It is worth noting that both BCS and SSIS can be used to expose data in O365. SSIS will require purchasing another third-party data connector to work with the REST API and OAuth. While BCS will require the use of Azure SQL and Data sync. (link to BCS + Azure blog)
Both BCS and SSIS can be used as a means to bring line of business data into SharePoint. Assuming you have no other requirements or limitations on which solution to use; it could be as simple as the old “custom versus out-of-the-box” question.