The Cloud Connector for Microsoft SharePoint 2010 and Office 365 takes your local line-of-business data to your cloud-based or any other hosted SharePoint - easy to connect, secure, always up-to-date, and bi-directional, if required. Content stored in almost any corporate data source, e.g. databases, files, ERP / CRM / ECMS or other legacy platforms and on-premise applications like Dynamics, SharePoint, Active Directory, SAP etc. is replicated to native SharePoint lists into the cloud via secure web services. Cloud-to-cloud connections e.g. to Azure, Twitter, Facebook, Google etc. are supported as well. All features of native SharePoint lists are completely available for your external data.  The Cloud Connector was specially developed to solve the specific challenges of remotely hosted disconnected systems in the cloud without any direct access to corporate business data and very restricted feature customization capabilities. A data-integration strategy needs to be within the foundation of your cloud computing plan. This integration needs to be innate to the architecture; it can't be something you're reminded of at the rollout meeting. The connector solves many issues that still exists with the Business Connectivity Services (BCS) on-premise and in the cloud.

Fig.: The Cloud Connector runs within the corporate network to push and pull data from/to native SharePoint lists in the Microsoft SharePoint 2010 and Office 365 cloud. See the video below for more.

• Push your external line-of-business data to a native SharePoint list into the cloud (or local) - always up-to-date. You don't need to program a web service that allows to pull data from outside.
• Extends the SharePoint cloud capabilities to existing data sets that will continue to reside within your corporate network, e.g. for compliance reasons.
• No programming or tools required. Setup in a few minutes guided by a wizard.
• No installation or customizing in the SharePoint cloud required.
• Bi-directional connections: If supported by driver. Several options to solve replication conflicts.
• Compatible to all SharePoint offerings, including on-premise, remotely hosted, sandboxed, e.g. Microsoft SharePoint Online / Microsoft Office 365.
• 100% Microsoft SharePoint Server 2010 compatible – Uses new CSOM API model for the cloud.
• Secure data connection and transfer: Connection via Web Services (SSL). Disconnected systems: A connection is only required for data exchange – not for list usage. No direct access to external company data required (“push” – no “pull” strategy). The application runs where your data resides today - on any server or client inside your corporate network.
• Virtually any data sources supported: ODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), SQL databases like MS SQL, SQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Dynamics NAV/CRM, Navision, SAP and many more. More complex data sources, web services, custom COM or .NET objects, can be connected using 3rd party add-ons. Cloud-to-cloud connections e.g. to salesforce.com,  Twitter, Facebook, Google etc.
• Start workflows in the cloud - on external data change: All features of native SharePoint lists are supported, e.g. views, sorting, filtering, grouping, mobile access, search, tagging & enterprise metadata, RSS feeds, change notifications per email or workflows in the cloud on external on-premise data change.
• Additional “SharePoint-only” columns can be created, e.g. for ranking, rating, comments or file attachments.
• Data aggregation: Multiple connections can rollup several different data sources to one list.
• High performance: Can manage large databases and lists (well tested with 100.000 items).
• Very cost-effective solution - easy to setup and maintain. Possibly you can save license costs for the connected on-premise systems, e.g. ERP/CRM user CALs.
• The connector is based on another very successful add-on, the Business Data List Connector für SharePoint (BDLC).

Fig.: The Cloud Connector supports almost any data sources via ODBC, OLEDB or MS .NET.

Fig.: The Cloud Connector is certified as Microsoft Platform Ready for Windows Azure

The connector is installed on a remote client (e.g. Windows 7) or server (Windows Server 2003/2008) directly in the company network, near the data source or inside DMZ with Intenet connnectivity required. 32-bit and 64-bit systems are supported. There is no installation or customizing required on the SharePoint system in the cloud.

Please use the installer provided to install the solution on a client (Windows 7) or server (Windows Server 2003/2008) in your corporate network with Internet connection (via HTTP) enabled and connectivity to your data source, e.g. to a database server. 32-bit systems are supported as well as 64-bit systems. Please note that your drivers must be 32-bit / 64-bit respectively. There is no installation or customizing required at the SharePoint Server in the cloud. The following three components are installed:

• Cloud Connection Service:  A windows service that operates in background to push / pull data as scheduled. You can connect manually for test and configuration as well.
• Cloud Connection Manager: Manage your connections here to stop/start, display current status etc. (starting with V3.0)
• Cloud Connection Wizzard: Assists the user to establish a connection between any corporate data source and native SharePoint 2010 lists in the cloud. (starting with V3.0)

To setup a connection manually please follow the documentation to provide XML-based connection files with the following information (see below for examples):

• Connection Provider: The driver layer to use, e.g. ODBC, OLEDB or any .NET based driver. The connector comes out-of-the-box with a Layer2 CSOM Driver (for SharePoint 2010) and a Layer2 OData Driver (for any OData protocol compatible sources). If no driver present, ask your admin to install. Client drivers mostly come with the database or other data source software. Please ask sales@layer2.de for drivers available. Export data to CSV and use ODBC in case no driver is available.
• Connection String: Information required to connect to the data source. See www.connectionstrings.com for more information. You will find many samples for connection strings in the Intenet, at your database vendor's web pages or in our FAQs.
• SharePoint List: Enter list URL address, authentication type, user name, password. Please use SSL/HTTPS for secure connections. Create the the list manually, if it not already exists. You can use any list type that fits your requirement, e.g. contacts, events, tasks or custom lists.
• SQL Query: Information required to reference your data selection, e.g. "Select * from myTable". You can give aliases for field names here, call views or stored procedures and use any functions that are supported by your data source and the driver layer.
• Field Assignment: Any field in your data source can be associated with a column in the SharePoint list. Please think carefully about different field types used on both sides (e.g. String, Integer). Name-based auto-mapping can be used.
• Primary Key(s): To update SharePoint items (rather then delete and re-insert) one or more primary key(s) (PK) is / are required - fields / columns that have uniue values, e.g. a customer # or a counter field. For many providers PKs are automatically set.
• Scheduling: List updates are pushed at specific dates / times or intervals, e.g. 4:00 am daily or every 15 minutes). List updates are processed in background using the windows service of the connector via web services.

How the Cloud Connector is licensed?
There is a completely FREE Shareware version available, that can manage up to 25 connections (SharePoint lists) with up to 25 items per list. The full version is licensed per server (where it is installed – not in the cloud, not per SharePoint server).

What is the Professional Edition?
There is a Professional edition, that can manage an unlimited number of items per list, but a limited number of 25 connections. You can have more than one Professional Editions installed on several servers, if required. Or you can use the Enterprise Edition with an unlimited number of connections.
For the Professional Edition one data entity must be SharePoint in any way, the other one can be whatever you want.

What is the Enterprise Edition?
The Enterprise version has no limitations with number of connections. Additionally the Enterprise Edition is not limited to SharePoint only. It can connect almost any systems, e.g. Twitter to SQL or SQL to Oracle (just to give an example).

Do I have to re-install to upgrade from Shareware to Professional / Enterprise version?
No, you will receive a modified license file only to place at the server to upgrade.

What is included in the annual Software Assurance (SA)?
Licence holders, who optionally acquire Software Assurance (SA) benefit from future improvements and new features of the licensed product. Software Assurance (SA) enables you to migrate your software from a lower-level software version to a higher-level version. Software Assurance (SA) makes available maintenance, updates, minor and major releases updates or upgrades. The Software Assurance (SA) is valid per 1 license (server) for 1 year from the date of product license purchase. It can be renewed after expiring. Additional services, which may be required for updating or upgrading, are not included.

Is there a version history available?
Yes, see FAQs for that.

What's the difference, compared to other EAI offerings?
The SharePoint Cloud Connector is highly specialized to SharePoint only. It is based on years of experience with integrating external data sources to SharePoint 2007 and 2010. SharePoint has very special data types, internal column names and mappings, web services and the Client Object Model API. There is no other EAI solution on the market, that fully supports connections to native SharePoint lists with all list features available in the cloud. One other difference is price and complexity: The Cloud Connector, especially the Professional version, is made for the mass market, also for small and medium enterprises. Every administrator should be able to configure a connection in just minutes - without any programming or customizing.

We would offer the connector to our clients as a reseller and / or implementer. Do you have a Partner Program?
Yes, partners receive a 30% discount on licenses in our online shop, a FREE NFR version for internal use, partner backlinks at product pages, 2nd level support and valid leads in their regions. Read more here.

Does the connector work with SharePoint Online and / or BPOS?
The connector requires SharePoint 2010 in the cloud. Microsoft is going to migrate SharePoint Online and BPOS clients currently to 2010. After migration is completed you can use the SharePoint Connector.

Does the connector work with other SharePoint offerings that are hosted externally?
Yes, so far they are SharePoint 2010 based.

Can I also use the connector for my internal SharePoint to connect external data sources to native SharePoint lists?
Generally yes. But there are other solutions, that possibly better support this specific scenario, e.g. the Business Data List Connector for SharePoint 2007 and 2010.

Does the connector support my specific data source?
You must have an ODBC, OLEDB or .NET based driver for your data source installed. Such drivers are available for almost any data sources. If there is no driver available, you can possibly export your data to Excel or Text files (CSV). This format is supported by the connector without any restrictions.

Can I replicate SAP data to the Offcie 365 cloud, SharePoint Online or any externally hosted SharePoint 2010?
Yes, you can access the SAP database directly via secure views using your data source driver, or you can use any 3rd party middleware for this. Export data to intermediate CSV-Files on a regulary base to connect to cloud could also be an option.

Which driver versions to use: 32-bit or 64-bit?
You have to use the driver version, that fits to your operating system. If no 64-bit driver is available yet, just install the connector at 32-bit Windows.

Can I start workflows in the cloud when records in my data source are changed?
Yes. If a primary key (e.g. a customer #) is configured in settings, items in the SharePoint cloud are updated rather then deleted and re-inserted. In this case you can start workflows in the cloud, when remote LOB data records are changed or newly created. Additionally ou can use RSS or email list notifications as usual with SharePoint.

Can I write-back changes made in SharePoint in the cloud back to the data source?
Yes that's included in V2.0. Your data source driver must support write access. There are several options available to manage possible replication conflicts, e.g. winner/looser definition on field level.

Can I move documents or files to the cloud using the connector?
Not with the current version. But this is planned for future versions.

Where I can find more technical information and samples?
You can find more information here in FAQs.

• A Windows Service is provided to operate your background updates automatically without any restrictions. Please note: When changes are made to the configuration files, the service has to be restarted. Alternatively you can force the connection service to run as application in foreground for better testing.
• Sample connection files are shown below for SQL Server, Excel and SharePoint as data sources to connect to SharePoint 2010 in the cloud.
• All field mapping is done by field names via XML. The SharePoint INTERNAL column names are used for mapping (not column titles). See log mapping check in log files for more information. In case of database connections, please use aliases in SQL (e.g. select myField as myAlias from myTable) to use the auto-mapping feature - or map columns manually.
• A "primary key" is required on both sides, data source and destination, e.g. a customer ID or SharePoint item ID. It can be a combination of multiple fields / columns (e.g. contact full name and company). In most cases (including most databases and SharePoint) it will be detected automatically.
• Primary keys must not be mapped. But it helps to understand what happens, if you have the primary keys available on both sides.
• You have to use the so called invariant name to declare a provider in the configuration file as data entity property (see your machine.config for installed providers). Typically the follwing providers are used:
  Layer2.SharePoint.Provider ... comes with the Cloud Connector to connect to SharePoint 2010 via CSOM (Client Side Object Model)
  Layer2.OData.Provider ... comes with the Cloud Connector to connect to any OData compatible data source. See www.odata.org for more.
  System.Data.Odbc ... the .NET Framework Data Provider for Odbc used to connect via ODBC
  System.Data.OleDb ... the .NET Famework Data Provider for OleDb used to conenct via OLEDB
  System.Data.OracleClient ... the .NET Framework Data Provider for Oracle used to connect to Oracle database
  System.Data.SqlClient ... the .NET Famework Data Provider for SqlServer used to connect to Microsoft SQL Server
  
  Please note: Provider names are case sensitive.
• Calendar lists are not supported yet because of an unsolved CSOM issue.
• User / Group columns that cannot be resolved at the destination system can cause circular updates. Please exclude this fields from auto-mapping.
• How to connect a SQL Server table to a native SharePoint 2010 / Office 365 list via CSOM?

Sample Connection File to connect the SQL Server Northwind sample table to a list "MyCloudList" at a site "MyCloudSite" at a SharePoint 2010 server "MySharePoint" one way only. A field / column CategoryID is used to process insert, update and delete operations on the SharePoint side as required on database changes with an interval of every hour. On the SharePoint side windows authentication is used by default. If used with Office 365, please add the authentication flag.

<?xml version="1.0" encoding="utf-8" ?>
<connection xmlns="http://www.layer2.de/schemas/cloudConnections" 
    interval="60" 
    enabled="true" 
    version="1.2">

<dataEntities>

    <dataEntity name="MyNorthwindDatabase"
        type="source"
        provider="System.Data.SqlClient"
        connectionString="Data Source=srvsql01; Initial Catalog=Northwind; User Id=MyID; Password=MyPassword"
        selectStatement="Select * From Categories">
    </dataEntity>

     <dataEntity name="MySharePoint"
      type="destination"
      provider="Layer2.SharePoint.Provider"
      connectionString="Data Source=http://MySharePoint.sharepoint.com/Sites/MyCloudSite/; Initial Catalog=MyCloudList; User Id=myusername@mydomain.onmicrosoft.com; Password=MyCloudPassword; Authentication=Office365">
 </dataEntity>

  </dataEntities>

</fieldMappings>

</connection>

• How to connect two native SharePoint 2010 / Office 365 lists via CSOM?

Sample Connection File to connect two SharePoint link lists "Links" in one direction.  The  ID column of the source list is mapped to a (previously created int) column SourceID in the destination list to process insert, update and delete operations on the destination side as required by source changes with an interval of every 10 minutes. The view myExport may a subset of all items only (e.g. using a flag export=true). The Select Statement currently is ignored in case of SharePoint provider. The primary key is optional. In case of SharePoint, the Item ID is used. To login with any given Microsoft Online Account as usual with Office 365 use  "Authentication=Office365" in the connection string with user name and password.

<?xml version="1.0" encoding="utf-8" ?>
<connection xmlns="http://www.layer2.de/schemas/cloudConnections"
interval="10"
enabled="true"
version="1.2">

<dataEntities>

With V2.0 bi-directional replication is available. Please simply remove the type (destination/source) from the data entity or set to "bidirectional" to activate this option. You can add some options for conflict resolution before the data entity sections, e.g.:

 <conflictResolution mode="warnAndContinue" /> 

You can add conflict resolution options on field level within the fieldMapping section, e.g.:

<fieldMapping>
  <conflictResolution mode="winnerLoser">
   <winningEntity name="SQLSource" />
  </conflictResolution>
  <field entity="SQLSource" name="ProductName"/>
  <field entity="SharePointDestination" name="Title" />
</fieldMapping>

You don't have to map the primary keys anymore starting with Version 2.0. See application manual for more information.

Microsoft Office 365 brings together cloud versions of the most trusted communication and collaboration products with the latest version of the Microsoft Office 2010 desktop suite for businesses of all sizes. Office 365 lets you quickly enhance and expand your collaboration and communication services with enterprise-class security and reliability. You don’t have to worry about costly server deployment or time-consuming server maintenance tasks. Because it’s designed to work with the Office applications your people already know, you can offer them the best productivity experience across the PC, phone, and browser—without heavily investing in retraining.
More information you will find at www.microsoft.com/office365.