As I watch all my customers leaves Notes/Domino behind, all I can do is try and assist as best I can. As part of this process I've found myself creating a Notes-to-SQL export tool.
The tool is Notes-based and involves the following three part process:
Step 1. Loop through and analyse every single document in the target database and build a list of all unique combinations of Form + Field + Data-Type. For each combination it finds, a Notes document is created which lists the Form name, the Field name and the type of data (text, Authors, Rich Text, Numbers etc)
The resulting list is then analysed and any duplicate fields (conflicting data types) or unnecessary fields are removed.
Step 2. Taking the results of step 1 it then builds the SQL tables (using the document's UNID as the table's primary key). For each form it creates a table of the same name. For each field related to that form it creates a column in the SQL table with the equivalent data type.
Step 3. Finally, step 3 populates the SQL tables with data by once again looping ALL documents in the database and exporting each document to a new row in SQL according to the map of form-fields we've decided on.
Once it's done we can then go in to SQL and define relationships between parent/child document by joining the tables in a Database Diagram. Although as an enhancement to the tool it could be a part of the export process that we define these rules.
As part of the initial configuration of the tool you can tell it which Forms to ignore, which fields ignore, which fields to always treat as UNIDs etc.
Once the data is in SQL then you can do the T and L of the ETL process.
For attachments there's a special table called Files which stores the UNID and filename of any files found during the export. These files are detached to disk and the path to them stored in the "Files" table. For any given row of any of the SQL tables you can find the associated attachments by looking in the Files table for a matching UNID.
Taking it To Market?
I've always known I need a product to sell. Could this be it? It's some way off being ready to sell directly off the shelf. However, with some support from yours truly I reckon it could be used in most situations or tailored to work where it doesn't quite fit.
If you're interested in using the tool to migrate data from Lotus Notes to SQL Server (or any other RDBMS system) then drop me a line on email@example.com.