I am trying to find the solution for data migration from LotusNotes to Sql Server without using third part tool. However, I have found out that client already has a license for LEI. Can I suggest them to use LEI for migrating data from Lotus Notes to Sql server. Is it possible? I need some pointers on this Note it is not scheduled data syncronization..
4 Answers
2
votes
I'm not sure LEI (http://www-01.ibm.com/software/lotus/products/enterpriseintegrator/) is the way to go. It was designed to allow data to be shared between Notes and some back-end database system. Note, I don't have any real experience with LEI so it very well may have some way to help you transfer data. My point is just that it wouldn't be my first choice since migrating data is not the primary purpose, from what I can tell. I also think it seems like overkill for a project that will likely require some manual interaction to fit the data into an RDBMS system.
Depending on the data you need to migrate, I would look to export the data out of Notes into Excel, and then pull it into SQL to fit the schema you've designed. You'll potentially save some headaches making this a two-step process.
Moving from a document-based database to a relational database will likely require some restructuring of your data. You may want to construct views in Notes that resemble the tables in your SQL database schema. You could then easily copy the views to Excel by selecting all the documents and clicking Edit > Copy Selected As Table, and then paste the results into Excel.
Another good option is to use the NotesSQL driver and pull data out via SQL Management Studio. This works great if you don't think there's any need to clean up the data in Excel before importing it.
Usually when moving data the one 'gotcha' will be around rich-text items. It's very difficult to export rich-text from Notes, and I usually recommend the Geniisoft Midas LSX for that. I haven't seen anything better (or even close) to what that tool can do. If you just need the text out of the rich text fields, though, the SQL driver does support extracting text-only from RT items.
Hope this helps!
0
votes
It's more usual to use LEI to keep an SQL db up to date with what's in the Notes db. That's what I've used it for previously. That said, LEI could certainly be used for a migration. You'll still have the same issue of cleaning up your data before the migration (eg. pick list fields with obsolete values).
Does the organization in question have someone who is proficient in LEI? If they do, then LEI is worth considering. The time needed to learn it from scratch might rule it out as a choice for your project. I know there is official IBM documentation for LEI; I don't know of any classes. I could not find any via google.
The previous post on exporting rich text items is well worth keeping in mind.
0
votes
You should definitely consider LEI if you have a license for it. As others have pointed out, it's designed for keeping data in sync with a RDBMS, but using it for a one shot migration also works great.
You will need to take the time to learn how to use the tool, but it gives you a lot of things for free that you would need to code around if you did something a little more manual. Using LEI also means that if you can be partially migrated before the final cut-over if you have a lot of data.
0
votes
I'm assuming this is one way task since you used "migration" instead of sync.
You could write an agent to export each document out as DXL (Domino XML). The DTDs come with your Lotus Notes/Domino install. Use XSLT to reformat to a preferred XML format and then import these into SQL.
Sample Lotus Script agent to export documents as DXL: https://www-304.ibm.com/support/docview.wss?uid=swg21206902 Sample SQL to bulk import XML files: http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx
