How to push database field content to SharePoint metadata columns (and write back)?

The Cloud Connector for Microsoft Office 365 and SharePoint connects almost any on-premise data source, e.g. ODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), SQL databases like SQL Server, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to native SharePoint lists in the Microsoft Cloud - or any other Microsoft SharePoint installation - in just minutes without any programming. For any questions please use the FAQ Web. Please contact [email protected] directly for more.

If you want to connect a native SharePoint list with a database source you possibly want to have your metadata information, e.g. creation and last modified date and time, author and editor names in SharePoint as well. This article explains this step-by-step.

Assuming you have a database table like that.

Fig.: Database table with metadata to map to SharePoint.

You can map the database fields to SharePoint internal columns in the Cloud Connector as follows.

Fig.: Field - Column mapping sample for metadata.

Please note the SharePoint side. You have to use these field names to write metadata to SharePoint. See the SharePoint list below for results:

Fig.: Sample list with metadata from database included as SharePoint metadata column.

This looks pretty simple, but need some pre-considerations to work.

• Modified / Created needs to be of type datetime
• Created By and Modified By must contain a valid user token in the target system. For example for Office 365 this can be i:0#.f|membership|[email protected].
• If you have the value myUser only in the database field user (e.g. from current user AD account) you can easily build the token in SQL, e.g.
  select *, 'i:0#.f|membership|' + user + '@mydomain.onmicrosoft.com' as SPuser from myTable.
  But be careful with bi-directional connections. It is better to keep the original field content in this case and build the token or user using triggers on database side.
• This also will work with person / group fields on SharePoint side.

Ready to go next steps?