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:
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.
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:
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:
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.
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.
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.
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.
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:
