Data Migration

The application includes data import functionality, which is designed to enable you to import relatively small data files into the system. The basis for this functionality is largely re-used for the data migration process; however, there are additional tools in place to aid bulk migration. This article is solely focused on the bulk migration process.

Preparing the Data File Format

In order to import data into the system, the file format needs to be in a particular format, following the conventions specified below.

The file format is double quote and comma delimited. However, the load will be slightly more efficient if you omit the leading double quote.

This is because the double quote and comma delimited format is not strictly supported by the SQL Server bulk insert functionality. The initial load imports the first double quote in the left-most field as if it is part of the field text, which then must be removed. If the leading double quote is not present in the first column, this step can be skipped.

Below is an example of a file format with the leading double quote included:

Below is an example of a file format with the leading double quote omitted:

The first row of the file is assumed to be a file header and as such, is omitted from the initial bulk insert. However, the file header isn’t used by the data migration process and thus can take on any format that you wish. Its purpose is solely to make the file header information available to humans viewing the data file directly.

Field Delimiter

To simulate support for double-quoted text, the field delimiter is three characters long. It is a comma encapsulated by two double quotes: “,”

The fields can therefore contain any text whatsoever, including double quotes, unless that text includes segments that match the exact pattern of the three-character field delimiter.

Row Terminator

The row terminator is a double quote followed by a carriage return. In other words, every row is on a separate line in the file and terminated with a double quote.

Below is an example of terminated rows:

NULL Values

Empty field values are interpreted by the bulk insert as NULL values and are imported that way. The double quotes themselves are not counted as field data as they are part of the delimiters. Therefore, a field entry such as this: “”,””, will be interpreted as two NULL value fields.

The word NULL appearing in a field will be interpreted as a string and the system will attempt to import the word “NULL”, possibly resulting in an error if the field in question does not support string-based data.

Data Migration Procedure

Access to Data Migration functionality is regulated by the CW Data Migration Admin Security Profile.

To access the Data Migration functionality, navigate to Settings > Configuration > Data Management > Data Import.

The Data Migration functions are displayed. These functions are described in the sections below.

Data Migration Maps

A Data Migration Map provides the mapping between the Business Object as it exists in the database and the data in the file.

Default mappings are shipped with each release of Care Cloud, but in the event they need to be edited or additional maps put in place for a migration process, you can access Data Migration Maps as follows:

1. Navigate to Settings > Configuration > Data Management > Data Import > Data Migration Maps. The Data Migration Maps screen is displayed.
2. Click + to create a new Data record or click on an existing record to view / edit it.

General

The following table describes the fields in the General section of the Data Migration Map record:

Administration

The following table describes the fields in the Administration section of the Data Migration Map record: 

Provide details of any changes (such as release number or date) that you made in the Change Log field.

TIP: Changes to the Data Migration Map which have been implemented by Advanced are also shown in this field. To view details of these changes, do the following:

— Select the relevant record on the Data Migration Maps screen.

— Click the Download Maps button on the toolbar.

— Open the Microsoft Word document in the downloaded folder.

Data Migration Field Maps

Data Migration Field Maps provide the mappings for specific fields in Care Cloud.

Default mappings should already be in place, but in the event they need to be edited or additional maps put in place for a migration process, you can access Data Migration Field Maps by clicking the Fields tab on the Data Migration Map record. An example is shown below:

Click on a row in the table to view the associated Data Migration Field Map record. An example is shown below:

The following table describes the fields in the Data Migration Field Map record:

Data Migration Packages

Data Migration Packages combine the Data Maps for the files to be imported.

To create a Data Migration Package:

1. Navigate to Settings > Configuration > Data Management > Data Import > Data Migration Packages. The Data Migration Packages screen is displayed.
2. Click + to create a new record.
3. Complete the fields described in the table below. (Mandatory fields are marked with a red asterisk). 

Once you've clicked Save, the Items tab becomes available. This allows you to create Data Migration Package Items. These records link the packages to the relevant Data Migration Maps.

Data Migration Package Items

To access Data Migration Package Items:

1. Click the Items tab on the Data Migration Package record.
2. Select the Create new record button.
3. Complete the fields described in the table below. (Mandatory fields are marked with a red asterisk). 

Exporting Data Migration Packages

To export Data Migration Packages, do the following:

1. Navigate to Settings > Configuration > Data Management > Data Import > Data Migration Packages.

The Data Migration Packages screen is displayed.

1. Select the checkboxes against the packages that you want to export.
2. Select the Download Packages button on the toolbar.

The packages will download in a ZIP file.

The ZIP file contains the following:

• A Word Document with the specification for the import files.
• An ImportConfig.xml which needs to be located in the same folder as the import files or, if the files are contained in sub-folders, the top-level folder containing the files.

Configuring the Data Import Tool

The Data Import Tool is a console application designed to move the import files to the staging folder on the database server and generate the data import process records using the CareDirector.DataImportService.

System Settings

The following System Settings are relevant to the Data Import process:

• DataImport.QueryExecutionTimeout: Maximum amount of time in seconds that we will allow for a single entity to be imported.
• SQLBasedImportLocalStagingFolder: Location of the database staging folder from the perspective of the database server.
• SQLBasedImportNetworkStagingFolder: Location of the database staging folder from the perspective of other machines on the network.

Configuring the app.config File

Update the following values in the app.config file:

• PlatFormServiceBaseUri: The Care Cloud URL.
• FileEncoding: Format of the import files (e.g. 'Default' or 'UTF-8').
• ExtractFolder: Location of the folder containing the import files and ImportConfig.xml file.
• •  UserName: User name that you normally use to access Care Cloud.

This task only needs to be carried out once if none of the above values change.

An example is shown below:

Running the Data Import Tool

Note: The Data Import files and the ImportConfig.xml file should be located in a folder that is accessible to the CareDirector.DataImportTool.

To run the data import tool, do the following:

1. Run the tool and a console-based screen will be displayed,

1. Enter the password associated with the user name provided in the app.config file and press Return.
2. Care Cloud does the following:
   1.  Import files are copied over to the database staging folder and Data Import Process records are generated in Care Cloud.
   2. Data Import Process records are processed by the CareDirector.DataImportService.

The service works by checking for any Data Import Process records with a Status of 'Not Started' or 'In Progress'. If any exist, it processes them synchronously in the order that they were created. For more information, see Monitoring the Data Import Tool below.

Note: There is a key in the app.config file by the name of ExecuteEveryXSeconds. This key controls the number of seconds the service will wait before seeking out more unprocessed Data Import Process records (set to 180 seconds by default but can be changed in advance of a Data Migration process).

Monitoring the Data Import Tool

Data Import Process records allow you to monitor the progress of an import process. A read-only Data Import Process record is automatically created for each data import file.

To access Data Import Process records and monitor the process of a Data Import, including errors that have occurred, do the following:

1. Navigate to Settings > Configuration > Data Management > Data Import > Data Import Processes. The Data Import Processes screen is displayed.
2. Select either of the following System Views:
   1. Pending Records: Shows all Data Import Process records with a Status of 'Not Started' or 'In Progress'.
   2. Processed Records: Shows all Data Import Process records with a Status of 'Finished', 'Finished with Errors' or 'Failed'.

The following columns in the Data Import Processes screen provides additional information about specific Data Import Process records:

• Items to Process: Total number of rows in the file to be processed.
• Successful Items: Total number of rows in the file that were successfully imported.
• Failed Items: Total number of rows in the file that were not imported due to an error.
• Ignored Items: Total number of rows in the file that were not imported due to incorrect data. This could be invalid data for a Target Field or missing mandatory fields.

1. Click on a Data Import Process record to access information about any errors or ignored items that occurred during the import process. The Data Import Process record is displayed.
2. Click the Menu tab on the Data Import Process record and select Data Import Process Errors in the Related Items category. The Data Import Process Errors pane is displayed.

The following columns are displayed in the Data Import Process Errors Pane:

• Created On: Date and time the error occurred.
• Row Id: row in the import file in which the error occurred.
• Field: Field in the import file in which the error occurred.
• Error Message: Error message generated.