Migrating from Ektron to Sitecore: Migration Tips

So you wanna migrate from Ektron to Sitecore …

Overview

Your client has chosen to migrate from Ektron to Sitecore. How can the data be migrated?

In this series, I’ll describe the process that our team recently went through for migrating Ektron content to Sitecore without a content migration tool, without the Ektron API, but by diving right into the database.

Be sure to read the intro and Ektron database overview posts first.

About Our Project

Our assignment was to convert two Ektron websites to Sitecore; one ported over with the same look and feel as the existing site (Project A); the other, a complete redesign with the existing content (Project B). Throughout the blog series, I’ll reference the two projects as Project A and Project B.
NOTE:  We worked with Ektron Version: 9.10 SP1 (Build 9.1.0.184).

Dependencies

There is alot of planning necessary when migrating content from one system to another.  After deciding what is going to be migrated to the new system, a migration order must be established.  In our project, much of the content was dependent on three areas:  the media library, taxonomy list and the People business area. (The client is a law firm so its people are its primary asset and they are tied to other business areas of the site like events, articles, and blogs). So, in the case of both of our projects, taxonomy is imported first, followed by media library, followed by People.

Migration Tip #1 – Migrate a Legacy Content Id to each Sitecore item

When building the Sitecore templates, make sure to include a single-line text field to house the Ektron Content ID.  This serves several purposes … it is used in migration to convert an Ektron relationship between two content items to a Sitecore relationship.  Also, with this ID, you can confidently re-run your migration script as many times as necessary without concern of having duplicate content within Sitecore (by always searching for the content ID before creating a new Sitecore item).

Migration Tip #2 – Retain the Ektron database indefinitely

This is important if the client chooses not to migrate 100% of the content.  Later, there may be a request to retrieve some unmigrated data.  Create several screenshots of the Ektron content tree so that the client has a familiar view for picking out content that wasn’t migrated so you can target it by folder path.  Then, use the content_folder_tbl’s FolderPath column to find the content.

Migration Tip #3 – Create a 301 redirect list while migrating

If your project is like mine, where several of the urls are changing, have an output of your migration be a mapping of old Ektron website url to new Sitecore website url and add these lists to your Sitecore Redirect Manager.

Migration Tip #4 – Update the Sitecore statistics with Ektron information

When saving imported content from Ektron, be sure to grab the admin fields for the Ektron content and update the __Created, __Created by, __Updated, and __Updated by fields.  See the next blog post for details on the Ektron to Sitecore mapping.

Migration Tip #5 – Add the alternate text to images and the title text to links at migration time

Like Sitecore, Ektron allows you to set the alternate text of an image when the image is referenced on a page.  Make sure to bring this over and if it’s not populated, suggest to the client that this is populated at content migration time with the image name (without the extension).

Migration Tip #6 – Fix the internal links in the rich text fields

Run your migration scripts a second time targeting the internal links that are in the rich text field.  You can use the 301 redirect table from tip #3 to assist.

Migration and Helix

As mentioned in the previous post, we opted to access the database directly within our Visual Studio project.

I started with a new project in the Foundation solution folder to house the the Entity Framework connection and some base classes that would change infrequently.

Namespace:  Foundation.ContentMigration.General

In this solution, I installed Entity Framework via the NuGet Package.

Install-Package EntityFramework

This Foundation project contains the EktronContentEntities model and a BaseRespository.cs class, a base class from which all business unit specific repositories inherit.  This contains the following:

  • instantiation of the EktronEntities class
  • pre-loading of the taxonomies from Sitecore
  • optional pre-loading of People from Sitecore
  • a simple method that pulled a list of items from Sitecore based on template from the index
  • methods to get taxonomies and people by Ektron content id

Each Feature project that has migrated content has a Content Migration repository class that inherits from this BaseRepository class.

I outline the contents of this feature project in the next blog.


Check out all posts for this series:

Advertisements

Migrating from Ektron to Sitecore: Ektron Database Tables

So you wanna migrate from Ektron to Sitecore …

Overview

Your client has chosen to migrate from Ektron to Sitecore. How can the data be migrated?

In this series, I’ll describe the process that our team recently went through for migrating Ektron content to Sitecore without a content migration tool, without the Ektron API, but by diving right into the database.

Be sure to read the intro post first.

About Our Project

Our assignment was to convert two Ektron websites to Sitecore; one ported over with the same look and feel as the existing site (Project A); the other, a complete redesign with the existing content (Project B). Throughout the blog series, I’ll reference the two projects as Project A and Project B.
NOTE:  We worked with Ektron Version: 9.10 SP1 (Build 9.1.0.184).

The Decision on Content Extraction Method

There are four primary ways to extract data from Ektron:

  • Third party migration mapping tool
  • Ektron’s Localization – Extract for Translation tool
  • Ektron Framework API
  • Extraction from SQL database

In my experience with a migration mapping tool, they come with a bit of a learning curve and tend to be a bit inflexible.  As a developer who likes to get her hands dirty, the cons outweighed the pros.

The Extract for Translation tool provides a third party with content for easy language translation and keeping the HTML in tact.  The XML format was a bit overwhelming and frankly not usable for content migration.  Here’s an example of the export:

translation tool

That left the two options that provide the most freedom and flexibility to the developer.  We evaluated using the Ektron Framework API; it seemed that GetItem() may meet the needs but ultimately ended up going with the extraction from the database because Entity Framework makes working with relational database tables a breeze.

Ektron Database Fundamentals

Below I discuss important fields and primary tables of the database where we extracted data.

Ektron Content Id

In Sitecore, the item’s guid is the unique identifier for an Item.  In Ektron, it is the Content Id.  Having the parent folder’s Content Id for each major business unit (e.g. products, orders, articles, contacts, etc)  of the site is the key to accessing the content from the database.  The admin tool is useful for this purpose.  In Ektron, navigate to the parent folder of the business unit and view the properties to retrieve the parent folder’s content id.

folder properties content id

 Content Table

The Ektron Content table is the center of the Ektron universe, much like the Sitecore Item table.  It contains the base content that you want to extract and import into Sitecore.

Let’s take a look at the table:

content table columns

Some of the fields are self-explanatory and their usage in  Sitecore greatly depends on how they were used in Ektron.  For instance, there two columns called “content_teaser” and “content_title” … these may or may not be consumed by the Ektron website code.

In the image above, I’ve highlighted some important columns.

content_html – This field contains most of the content of the page, in XML format that was built in Ektron with a Smart Form.
Example:
xml example

folder_id – This field is populated if the content is logically nested underneath a folder in the Ektron content tree.  Using the content id of the folder of a business unit (as described above), you can simply query for all children to get the content.

SELECT *
 FROM [EktronDB].[dbo].[content] 
 where folder_id = 17179882623 -- the parent folder's content id
 order by content_title desc

end_date – This field is populated when the content has been archived within Ektron. Eventually, it can be mapped to the Archive Date field Careful with this one … If this field is mapped to the Archive date field in Sitecore right away, the content will be moved to the Archived database.

Taxonomy Tables

The Ektron Taxonomy Tables hold the relationships between the content and the taxonomy structure.  For instance, if a piece of content contains a select list field of US States, this is where you’re going to find all states related to that content.

taxonomy tables

Here’s an example query:

SELECT c.*, ti.*, tx.*
 FROM [EktronDB].[dbo].[content] c 
 JOIN [EktronDB].[dbo].[taxonomy_item_tbl] ti on ti.taxonomy_item_id = c.content_id
 JOIN [EktronDB].[dbo].[taxonomy_tbl] tx on ti.taxonomy_id = tx.taxonomy_id
 where c.content_id = 10737421044 -- insert your content id
 and tx.taxonomy_language_id = 1033 -- 1033 = English in Ektron

Metadata Tables

The Ektron Metadata tables have the potential to hold a variety of data for a piece of content.  In our project, the metadata stored the checkbox value for which callouts were displayed in the sidebar for a piece of content. It also was used to store some standard content fields that were eventually displayed on the website.

meta tables

Example query:

SELECT c.content_id, t.meta_name, m.meta_value
 FROM [EktronDb].[dbo].[content] c
 JOIN [EktronDb].[dbo].[content_meta_tbl] m on c.content_id = m.content_id
 JOIN [EktronDb].[dbo].[metadata_type] t on t.meta_type_id = m.meta_type_id 
 where folder_id = 1719881363 -- insert your content id
 and m.meta_value <> ''
 and m.active=1

UrlAliasMapping Table

Since we are dealing with webforms and most Ektron detail pages used this url convention, url.com/?id=171881363, we opted to take advantage of the alias table which the client used to provide friendly urls in marketing materials.

urlalias.png

Example query:

SELECT *
 FROM [EktronDb].[dbo].[content] c
 JOIN [EktronDb].[dbo].[UrlAliasMapping] u on c.content_id = u.targetid
 where c.folder_id = 17179869710

Content Folder table

The content_folder_tbl is handy to look up the folder structure that the Ektron admin has.  It contains the folder IDs, folder paths, event the folder ID paths for each folder.

content folder table

Example query:

SELECT * [folder_id]
 ,[folder_name]
 ,[FolderPath]
 ,[FolderIdPath]
 FROM [EktronDB].[dbo].[content_folder_tbl]
 where FolderPath like 'Lists%'
 order by FolderPath asc

 


Check out all posts for this series: