SharePoint Consulting & BCS Trouble-shooting
SharePoint consultants, developers, and power users have had a love-hate relationship with SharePoint’s BCS (Business Connectivity Services) 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.
Why SSIS is more suitable than Business Connectivity Services for most situations
The solution to this is to use SSIS (SQL Server Integration Services) to push data from SQL (or other data sources) to SharePoint. An SSIS package can be as simple as Ex. 1, a one-time and one-way push from an external data
source to a SharePoint list. Once in SharePoint, users can work with that data as if it had been manually created within SharePoint.
If data needs to be updated (nightly, hourly, or every minute), Ex. 2 would be a more suitable choice which allows data to be synchronized in a single direction. Unlike BCS, SSIS can be used to extract data from SharePoint and push it to a data warehouse for analysis and reporting (or any other destination that SSIS has an adapter for).
Below are some of the advantages of SSIS over BCS (or BDC depending on the SharePoint version):
Ex. 1
- Pull Data from a data source (typically SQL)
- Push data to a SharePoint Destination
Ex. 2
- Get the data
- Pull from the primary data source
- Pull from the SharePoint list
- Merge the data based on some business unique identifier (Well ID, Product SKU, or Student ID)
- Push new records and updates to the SharePoint list
Pros
- Very efficient; capable of doing thousands of rows per minute
- Rapid development
- Great for one-way data push
- External to SharePoint
- SharePoint to External
- The primary data source (SQL Server) can be taken offline for periods of time without disrupting SharePoint
- SSIS can pull data from many sources
- SAP
- Siebel
- Excel, XML, or CSV
- Sales force
- Hadoop
- HTTP Web Services
- SQL
- Oracle
- Sybase
- IBM DB2
- MySQL
- MS Access
Cons
- Requires someone with SSIS knowledge, or at the very least strong SQL skills
- Requires an SQL Server Integration Services instance
- Two-Way synchronization is non-trivial (resolving multiple changes to a single record)
For more on our SharePoint consulting work, check out this page.