How to use the SQL import utility

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  

The SQL Import utility allows you to import data from a Microsoft SQL Server database as new documents into Kentico CMS content repository. 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 return 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 box Import file. 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 Kentico CMS database.

 

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