Sunday 17 November 2013

Document migration from OpenText LiveLink ECM - eDocs R/KYV 9.1 to SharePoint 2010

One of my recent engagements involved investigating the export of documents from a customised LiveLink ECM system with the intention of uploading the content into SharePoint 2010.

The Challenge

LiveLink stores documents in an Oracle database with Web site and desktop client UIs.

Documents are logically stored as nested classes and folders.

There are two internal path representations for folders in .

Documents have associated metadata which is stored various tables within the database.

Both documents and metadata need to be replicated in SharePoint.

The solution

The solution comprises of multiple components.

Export application

A custom .net windows application was designed to export the both the documents and metadata from LiveLink. The application takes various parameters, one of which is the root folder path. The application will iterate through each of the sub folder recursively. During this process a physical folder on the local file system is created for each LiveLink folder and each document is download with the metadata captured. The result is a local file system representation of the folders in LiveLink. A custom .net windows application was designed to export the both the documents and metadata from LiveLink. The application takes various parameters, one of which is the root folder path. The application will iterate through each of the sub folder recursively. During this process a physical folder on the local file system is created for each LiveLink folder and each document is download with the metadata captured. The result is a local file system representation of the folders in LiveLink.

The application utilises System.Data.OracleClient to allow direct communication with the Oracle LiveLink database. This allows the folder and file structure to be determined.

The document download has been implemented by making SOAP calls to LiveLink where the document ID is passed as a parameter. Having only limited knowledge of LiveLink and any of its APIs made this quite tricky but it is possible. I had attempted extracting the documents directly from Oracle by downloading the files as blobs but there were issues. Specifically the files were corrupted which had the result of MS Word documents losing their formatting. Not exactly what we want from and EDRM export. SOAP calls however allowed for successful download with no issues.

The metadata is captured in a csv file where there is one row entered per document. The csv file captures the location of the document on the file system, the author, the date and other metadata properties.

Once the tool has completed running there is a local copy of all the files and folders stored and a csv file containing all the captured metadata.

Import tool

My initial assumption was that I would use some PowerShell to upload the files into SharePoint. Obviously being a SharePoint geek this is where I wanted to go. However I wanted to give some flexibility to my client so that they were able to do document imports without being reliant on PowerShell knowledge. The ShareGate migration tool served the need well. This product allows a bulk up load of files and folderd complete with Metadata into SharePoint. Where the genius comes in is that you can specify an MS Excel file to act as a source for the upload. It will go through each line in the excel sheet and upload the file specified in the path with its metadata as specified in the other columns. You can map each column in excel to your column in your SharePoint content type. I have to admit I did think it would meet our need but was extremely pleased when I got it working. As was my client.

So there you go, it is possible to migrate content from LiveLink to SharePoint 2010....but lets be honest, we always knew it was deep down!