[TechSpeak] Sitecore Data Transfer — Tool SQL BULK INSERT

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:

Reading Sitecore Items From the Master Database

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

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:

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:

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:

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 – =^.^=

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:

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:

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:

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:

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

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

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!