How to use the SQL import utility

The SQL Import utility allows you to import data from a Microsoft SQL Server database as new documents into the Kentico CMS content repository (content tree). If your source data is in a different format, you may need to import them to some Microsoft SQL Server database using Microsoft SQL Server tools.

Before you run the SQL Import utility, you need to configure its parameters. The utility is stored in folder <Kentico CMS installation in Program Files>\SQLImport. Open the SQLImport.exe.config file and set the following values:

Key	Description	Sample Value
SourceConnectionString	Connection string to the source database.	<add name="SourceConnectionString" connectionString="Persist Security Info=False;database=mysourcedb;server=myserver;user id=sa;password=mypassword;Current Language=English;Connection Timeout=120;" />
CMSConnectionString	Connection string to the Kentico CMS database. You can copy this value from the web.config file of your web project.	<add name="CMSConnectionString" connectionString="Persist Security Info=False;database=mycmsdb;server=myserver;user id=sa;password=mypassword;Current Language=English;Connection Timeout=120;" />
CMSDefaultUserID	UserID value of the user who will be author of the imported files. The default value is 53 for the administrator user account. You can find other values in the CMS_User table.	<add key="CMSDefaultUserID" value="53" />
ImportFilesDiskPath	The physical disk path to the folder with files to be imported (in case you are importing files together with data).	<add key="ImportFilesDiskPath" value="c:\Temp" />
WebApplicationPhysicalPath	Path to the root of the web project folder (in case the target site is set to save files to disk [Site Manager -> Settings -> File -> Store files in filesystem]).	<add key="WebApplicationPhysicalPath" value="C:\Website" />

Now you can start the import utility using SQLImport.exe. The import wizard starts:

devguide_clip0201

Click Next. Select the target website:

devguide_clip0202

Click Next. Select the target content culture:

devguide_clip0203

Click Next. Enter the SQL query that will retrieve the data to be imported from the source database:

devguide_clip0204

Click Next. Select the type of the imported documents.

devguide_clip0205

Click Next. Map the source and target fields and check the fields that should be imported. You can add the source fields manually multiple times if you need to import one source field into several target fields.

If the source field contains the path to the file, check the Import file box. In this case, the source field must contain a relative path to the path in the ImportFilesDiskPath web.config parameter - both values are joined and the file is imported from the specified location.

devguide_clip0206

Click Next. Select the field that will be used for the name of the document:

devguide_clip0207

Click Next. Select the target alias path where the documents should be imported:

devguide_clip0208

Click Next. Review the import settings:

devguide_clip0209

Now the data is imported into the Kentico CMS database.

Page url: http://devnet.kentico.com/docs/5_5r2/devguide/index.html?how_to_use_the_sql_import_utility.htm