[TechSpeak] Sitecore Data Transfer — Tool SQL BULK INSERT

Updated Feb 3, 2023

Sometimes, we need to import Sitecore content data in the scope of migration or deploying process. In general, we can use Sitecore Migrations tools (if we need full migration). But if we just need to export content data, we can use Sitecore Package Designer, Unicorn or TDS.

In my case, we needed to migrate dozens of content items (more than 40 000) from Sitecore 7.5 to Sitecore 8.2. And we had only Sitecore Package Designer.

The main issue we faced was the long processing time (a couple of hours) of Sitecore Package Designer. This was the trigger for us to release our Sitecore Data Transfer tool

This tool saved us.

Sitecore Data Transfer tool consists of the following parts:

  1. Configuration
  2. Reading Sitecore items from the master database
  3. Serialize #2 to non-XML format files for SQL BULK INSERT
  4. BULK INSERT to target Sitecore DB

Configuration

There is a SerializingOptions.json configuration file. It should be configured before running the tool:

Sitecore-Data-Transfer-Tool-photo

Reading Sitecore Items From the Master Database

EntityFramework plays a role here. There is a SitecoreDB Context with the following DB Sets defined:

Sitecore-data-transfer-photo

As you can see, the following tables are used in the Sitecore master database:

  • Items
  • Descendants
  • SharedFields
  • UnversionedFields
  • VersionedFields

Yes, to make a content data transfer we need these 5 tables.

The ScRepository implements IScRepository interface to get data from the SitecoreDB context:

IScRepository

Serialize #2 to non-XML Format Files for SQL BULK INSERT

When data is read from EF context into the object collection, the tool serializes the collection to the appropriate format files for future SQL BULK INSERT.

In the configuration file (step #1) you can see the following properties:

Sitecore-data-transfer-photo

These properties tell us that the tool serializes data into five separate files: one for Items, one for Shared Fields, one for Versioned Fields, one for Unversioned Fields, one for Descendants.

Inside this file, you can observe the following text:

Sitecore-data-transfer-Formatted-Text-photo

A bit messy, right? It is predictable — we serialized dozens of SQL records into a file. After each column, you can see a “cat” terminator – =^.^=

Sitecore-data-migration-photo

After each record, you can see a “pig” terminator – <(*(oo)*) You can specify your terminators in the config file:

“FieldsTerminator”: “=^.^=”,

“RowsTerminator”: “(*(oo)*)”,

All files are written in Unicode encoding.

BULK INSERT to Target Sitecore DB

Here is the final step. An SQL Script reads data files prepared in step #3 and launches BULK INSERT into a target Sitecore Master DB:

SQL_BULK_INSERT

And that is it.

How to run this tool? You need to clone or download it from here.

Open the solution, make a build and run ScDataTransfer.UI project (this is a console application). You will see a simple screen:

SQL-BULK-INSERT-Run-Options-photo

If you want to show your config — just press 1

If you want to run the serialization process — just press 2

After, you will see quite the same picture:

SQL-BULK-INSERT-Time-Spent-photo

When you see “Done!” log message — it means that the Serialization process is completed and you can observe the following files into the App_data folder:

Sitecore-data-transfer-Result-Files-photo

To complete the whole content data transferring, we need to perform some more steps:

  • Make sure to export just a root item from the old Sitecore instance to a new one
  • Use SQL Management studio to execute BULK INSERT SCRIPT
  • Log into your target Sitecore Admin Panel — Control Panel — Rebuild link databases
RebuildLinks
  • Certainly, you can publish migrated content if needed

That is it! If you would like to ask how much time it takes to get target items, serialize them into files and run the bulk script — in my case, it took approx two minutes.

Below you can see the BULK import output:

Sitecore-content-items-transfer-photo

43 476 rows of content items
391 187 rows of versioned fields
3 rows of unversioned fields
260 853 rows of descendants

If you have any questions/issues, feel free to contribute to this tool and contact me!

Happy coding, Sitecorians.

P.S. Many thanks to my colleague Vitaly Ivanov who was the initiator and main developer of this tool!

4 Comments
Leave a comment
  • Pretty part of content. I just stumbled upon your web site and say that I get actually enjoyed account your blog posts. Anyway I will be subscribing in your feeds.

  • Thank you for the auspicious writeup. It in fact was a amusement account it.
    Look advanced to far added agreeable from you!

  • Hi there, You’ve done a fantastic job. I’ll certainly digg it and personally suggest
    to my friends. I’m confident they will be benefited from this web site.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>