Sunday, January 25, 2015

Sitecore: Tips & Techniques on Data Migration into sitecore cms



Sitecore: Tips & Techniques on Data Migration into sitecore cms

Now it’s common to migrate data from existing database to sitecore Items.

We had a same requirement, one of my client wanted to migrate their old web site(that was in SQl server database) to Sitecore 7.0.

The challenge was to migrate around 20k events items, 30k news,40k publications etc along with media items like PDFS,Audio,Videos,Images etc into sitecore.

So after giving many efforts/time, getting help from sitecore, re-coding the utility, we found some take away points, from this data migration utility.

So here I want to share my experience that may be useful to you and save your time,below are the key points that should be take care at the time of creation the data migration utility.


  • Disable all indexes during the migration
  • Wrap the your custom logic into: SecurityDisabler(), EventDisabler(), ProxyDisabler()
  • Create store procedure in Sql server instead of direct query.
  • Create web API( controllers) to run the utility instead of separate page or any other technology(get rid of the time out problem)
  • Divide the data into parameter functions, instead to run utility as whole divide it into chunks that would call through controller.
  • Logging the utility, log record and exception so that it would be traced if needed.
  • Write the utility in such a way that would easily updated instead of deleting all item and re-run(it’s really very time consuming).
  • Use proper check like DBNULL and not null condition in code.
  • Assign item id for reference field include media items.
  • Use ProposeValidItemName function while creating item,More Detail
  • Proper handling for date field. I am enclosing the code below.
  • Use optimize way to create media items like image,pdf,videos,More detail
  • Skip the item that create error and log properly instead of application crash.
  • Effectively handle folder creation, don’t create more than 100 items under one folder, create the folder on basis of some logic like Year,Month,date or Bucketable folder etc.


Below are the some code snippets:

Get data from Store procedure in c#:

public DataTable GetSPData(string spName, string parentFolder, string childFolder)
        {
            string connString =” your connection string ";
            string sql = spName;
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(sql, conn);

                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                if (string.IsNullOrEmpty(parentFolder) == false && string.IsNullOrEmpty(childFolder) == false)
                {
                    da.SelectCommand.Parameters.Add(new SqlParameter("@parameter name", parentFolder));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@parameter name, childFolder));
                }

                DataSet ds = new DataSet();
                da.Fill(ds, "result_name");
                DataTable dt = ds.Tables["result_name"];
                return dt;
                //foreach (DataRow row in dt.Rows)
                //{
                //    //manipulate your data
                //}

            }
            catch (Exception ex)
            {
                            }
            finally
            {
                conn.Close();
            }


            return null;

        }



Get and create sitecore Item C#

  public Item GetAndCreateItem(string ItemName, TemplateItem currentTemplate, Item parentItem)
        {
            try
            {
                                if (parentItem != null)
                {
                    Item childItem = parentItem.Axes.GetDescendant(Sitecore.Data.Items.ItemUtil.ProposeValidItemName(ItemName));
                    if (childItem != null)
                    {
                        return childItem;
                    }
                    else
                    {
                        using (new SecurityDisabler())
                        {
                            childItem = parentItem.Add(Sitecore.Data.Items.ItemUtil.ProposeValidItemName(ItemName), currentTemplate);
                            return childItem;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
               

            return null;
        }



Custom Loggingin sitecore

System.IO.File.AppendAllText(“file path”,Error);

I hope this post would useful to you regrading data migration into sitecore.

Happy Coding !!