Using Talend Studio DI as ETL Tool for Lotus Notes Domino Database
I haven’t done much writing for a while but that doesn’t mean I am dead – literally and figuratively! LOL. I guess am just busy exploring, maybe traveling around to different cities, venturing into business and attending to personal stuffs that I forgot to pause and write some fascinating discoveries. Well that’s a different story. But then, here I am. Still alive and writing. Happy New Year 2017! đź™‚
So while I was away for ‘ages’, there were lots of discoveries that I wanted to share. One of which is the tool called Talend Studio for Data Integration.
I’m still a Cognos Aficionado, by the way, and Cognos Data Manager is definitely a great tool but because of licensing issues we got in the office I was compelled to use or look for another ETL tool. Definitely an open source this time around, i told to myself. Well lucky enough, I found 2 great ETL tools worthy of my time to explore i.e. Pentaho Kettle and Talend Studio DI. Both of which are salubrious enough to solve any ETL related tasks including connectivity between traditional and non-traditional databases. More than that, it has connectors especially designed for IBM Lotus Notes Domino Databases. For this post, I am leaning towards the Talend Studio DI. It has a lot of cool and promising features which i am looking forward to in their next releases not to mention their seamless integration to java technology. Somehow the User Interface of Talend Studio DI is somewhat ‘eclipse’ like which am pretty sure this can be integrated to eclipse platform as a separate window perspective.
Download Talend and tnotesinput connectors from Talend Exchange
Download them here
Install our components in a new directory under your Talend directory.
In Windows -> Preferences -> Talend -> Components, change the User Component path to your extracted path.
tNotesInputÂ â€“ Reads data from a View (or @Function Select formula) and exposes as rows within Talend IDE.
tNotesOutputÂ â€“ Write data to a database (Inserts and Updates). You need a key to be able to use the Update function.
tNotesRunAgentÂ â€“ Runs a background agent in Lotus Notes Domino
For tNotesInput to work, you need to load the Notes.jar library. This file is available from either the Client or the Server. So, your first component in your job should be tLibraryLoad, which will declare Notes.jar
DIIOP â€“ Allows you to bypass the local Client and instead execute remote instructions to the server. Minimizes the footprint on the client, but slows down processing. This option is necessary if you are working in 64-bit client environments.
Set the iNotes user id and password if you use DIIOP. Also, specify your server address for the DIIOP port. If you are using a local copy of the nsf database, just leave this blank.
Use the local client Password (current .ID file) if you do not use DIIOP
Domino Server & File/Path Name â€“ Server can be local (leave as â€śâ€ť), or enter the server name here. File/Path should use forward slashes (like in the example)
ViewName â€“ The name of the view you will â€śreadâ€ť. Alternatively, you can select the @Formula checkbox. When checked, you can specify a Lotus Notes Select formula instead of a view (return a NotesDocumentCollection).
ReverseIndex â€“ Read from the bottom of the View (or Collection) instead of the top.
# Docs To Return â€“ Leave ALL if you want all docs in the view. Otherwise, specify the limit (integer).
Schema: The schema needs to be declared (since every notes entity can have unlimited fields). To do so, please review the following picture. IT shows an example schema for an Email message in Notes. Note how you can override Field names (Db Column) with an @Function formula placed in the Comment box.
Please click the image to enlarge
I am using Talend MDM Studio 6 version. Â This is a better version than the Talend Data Integration 5.0. Talend 6 uses Java 1.8 version, so you may want to install java version 8 before you upgrade to Talend 6.
For more information, feel free to browse overÂ the Techronnati boards