IBM Lotus Notes Domino Data Integration and Synchronization

​The Layer2 Cloud Connector can be used to connect and sync almost any external data source to IBM Lotus Notes and Domino via ODBC codeless. This page gives an example about how to connect and sync Notes data with Microsoft SharePoint or Office 365.

Fig.: IBM Lotus Notes and Domino can be connected and synced with almost any external data source, including SharePoint or Office 365.

IBM Lotus Notes ODBC Setup

​To verify the following steps you need a working Notes environment. You can download a free trial of IBM Domino 9.0.1 from the IBM Lotus Notes web site after registration, if required. Additionally you need the ODBC driver installed. You can download here.

Fig.: Download the Notes / Domino ODBC driver from IBM web page.

Click „Continue“/“Confirm“ on the next pages and register, if required. This leads you to the following page where you can initiate the download.

Fig.: Please use the 32 Bit Version of the IBM ODBC Driver.

Please unzip and install the 32 Bit version of the IBM ODBC Driver. Note that is must fit the Layer2 Cloud Connector version (also 32-bit). If the connector and driver version do not fit regarding 32/64-bit you will receive the following error later on:

Fig.: Your ODBC driver may not fit to the Cloud Connector version in terms of 32/64-bit.

Please uninstall the Layer2 Cloud Connector and the IBM ODBC Driver and install the 32-bit version of both tools in this case.

If you don‘t want to test with your production databases, you can download a sample database here. Be aware that you need to have a database with a primary key field if you plan to sync bi-directional. The sample database linked above can only be used for uni-directional synchronization from Notes to SharePoint or to other destinations.

For IBM ODBC to work as expected you need to to add the path of Lotus NotesSQL, notes.ini and Lotus Notes Client to the environment variables as follows:

1. Open PC Properties (right-click on PC icon in Explorer)
2. Click on Advanced Settings
3. Click on Advanced tab of the dialog and then Environment Variables
4. Under System Variables select and edit Path to add the required paths, i.e.
a. For notes.ini: C:\Users\<username>\AppData\Local\IBM\Notes\Data
b. For Notes Client: C:\Program Files (x86)\IBM\Notes
c. For NotesSQL: C:\NotesSQL

Please double-check whether the paths are the same at your system. You may have to reboot after this change.

Now as a final step you have to setup an ODBC DSN as follows.

Fig.: ODBC DSN setup for Lotus Notes.

Please Open ODBC Administrator (32bit) , go to System DSN tab and choose Add. On the options tab please fill the following information:

Fig.: Fill the option tab of Lotus Notes ODBC.

Please provide the following information:

• A name for your data source
• A description for your data source
• The Domino server – will be „local“ in this test case
• The full path to the SharePointTest.nfs file (click Browse to search for it)
• The user name (click Add user and then Browse to open the user.id file that fills in your username)

Note: The password needs not to be set in this scenario.

How to connect and sync IBM Lotus Notes data with SharePoint?

​Now we are ready to connect and sync the table "Companies" in an IBM Lotus Notes with SharePoint contacts.

Fig.: IBM Lotus Notes table to be synced with SharePoint

Let's create a new connection in the Layer2 Cloud Connector Connection Manager as follows. 

 Fig.: Create a new connection in the Layer2 Cloud Connector Connection Manager.

 Fig.: Notes to SharePoint or Office 365 connection in the Connection Manager.

You should first define and test the connection carefully and set scheduling later on. Let's start with the Notes data source by providing the following information:

• Connection Title
• Data Provider: Odbc Data Provider
• Connection String: DNS=SPTest; User ID=myUser; Password=MyPassword
  
  Please use your ODBC DSN name here. User ID and Password only if required. You can also directly attach Notes databases without having a DSN as shown in the screen below.
• Select Statement: "Select * from Companies"
  
  

Please use any valid Notes SQL query statement. It will be passed directly to the ODBC driver. 

 Fig.: Data source configuration in the Layer2 Cloud Connector Connection Manager.

Please verify connection string and display a data preview. Now let's configure the data destination, SharePoint, Office 365 or any other system like SQL, Exchange, ERP/CRM e.g. SAP, Dynamics, Navision or others. Note that you have to create the target list before.

Fig.: Data destination configuration in the Layer2 Cloud Connector (SharePoint as an example, but can be any)

Please provide the following information:

• Connection Title: e.g. SharePoint
• Data Provider: Layer2 Data Provider for SharePoint (CSOM)
• Connection String: Depending on Authentication and other properties. See here for specification.
• Primary Key: Not required, SharePoint Item ID is taken automatically.
• Replication Key: Not used in this case.
• Disable Operations: Not used in this sample but can be helpful, e.g. to prevent deletes of existing SharePoint items.

Please verify connection string and display a data preview. Now it comes to field / column mapping between data source and destination. 

Fig.: Data source field to target column mapping. SharePoint contact list columns are used in this sample.

Please verify mapping. If all fits, we are ready for first sync.

Fig.: First data synchronization from Notes to SharePoint. Two new records are inserted.

Please press "Run Now" for first sync. You data should be inserted. Take care about possible issues, e.g. data conversion issues and fix it, e.g. in mapping. Run sync again. There should be no changes in case of an unchanged Notes database. You Notes data are now synced with SharePoint or Office 365 like this.

 Fig.: IBM Notes data in Microsoft SharePoint or Office 365.

You can now go back to the connection settings and set scheduling as required. E.g. you can sync every 10 minutes, per hour or daily - depending on amount of changes and requirements.

IBM Lotus Notes data integration and synchronization: Next Steps

Please note the following:

• It's not about bulk import. Changed items are synced only. You can enable change notifications or workflows on the SharePoint side to notify your users about changes in Notes.
• Columns that are not mapped are left untouched. E.g. you add additional fields or attachments to remain "SharePoint only".
• You can sync subsets only using views on the Notes side.
• You have all options of a native SharePoint contact list, e.g. mobile and offline display, sync to any devices, connect to Outlook etc.
• Notes to SharePoint as target system is just an example: You could also sync Notes data with SQL databases, systems like ERP/CRM, (e.g. SAP, Microsoft CRM or NAV via OData), XML, Excel / CSV, Access, web services etc. Your users are working with the SharePoint contacts in this case - no connectivity or license is required for the external system (Notes). Why not syncing your notes contacts to Microsoft CRM?
• You can also sync to other SharePoint list types, e.g. tasks, calendar or custom.
• You don't need any coding or installation / configuration changes on both data entities.
• You don't have to open your network in case of a sync to Office 365, Azure cloud etc. The Cloud Connector service runs as a "man in the middle" inside your network on a Windows client ot server (must not be SharePoint or Notes server) and manages any changes.
• You can generally sync uni-directional or bi-directional, if supported by data provider, data query and access rights. More sync options are optionally available (e.g. no delete, no insert, no update etc.)
• You can also use the Layer2 Business Data List Connector for SharePoint on-premises (instead of the Cloud Connector) with 64-bit data providers.

You can download and evaluate the Layer2 Cloud Connector today and use as free shareware. For full features you can license the tool online on the product page. If you have questions, please contact [email protected] directly.

Ready to go next steps?