Data Migration

Managing Your Data Migration Process

Rick

When your data migration process breaks, how do you fix it and restart it?

DISCLAIMER: This blog article assumes you are familiar with creating a scoped app, importing data, using update sets, configuring form fields, creating custom tables, configuring list layouts and how to use the ServiceNow UI.

This data migration management solution helps answer these questions:

  1. How do I know if the data migration process breaks?
  2. How do I know where the process broke?
  3. How do I restart the process at the point where it broke without having to restart the entire process?

I’ve had many data migration projects where I’m migrating external data into ServiceNow or getting data from another ServiceNow custom application into a new custom application. This blog article walks through the process for external data, but can be modified to work with data that already exists in ServiceNow in a different custom application.

I could have used the typical system import process and write transform map scripts to transform the migrated data into the ServiceNow table. My challenge has always been – what if the process breaks before it’s completed? How do I know on what record it broke without having to pour through hundreds or thousands of log entries?

What if the process breaks – how do I know where it broke?

If the data migration process breaks, I needed to know where it stopped working so I could troubleshoot the transform code and then restart the process at the point it broke without having to restart the entire process. Follow along to see what I did to accomplish this–there are steps later that allow you to complete the entire process on your Personal Developer Instance (PDI). Here’s my process overview:

  1. Create a scoped app (this blog article creates a new scoped app, but these concepts can be applied to any data migration process)
  2. Create a Case Management custom table.
  3. Create a custom data migration tracker table to track the data migration process.
  4. Create a Data Migration Process Admin user.
  5. Get the external data (xlsx) into ServiceNow.
  6. Populate the custom data migration tracker with the imported data sys_ids and number.
  7. Get data from the tracker table to create ServiceNow records.
  8. Verify that the created ServiceNow records match the imported data.

Follow along in your Personal Developer Instance (PDI) to see how this works

1. Create a scoped app. The first thing that needs to be done is to create a new scoped application where external data will be used to create case management records

Open Studio (not the App Engine Studio) in your PDI and create a new custom scoped application called “Scooby Do Case Management“

2. Create a Case Management custom table

Within the Scooby Do Case Management scoped app, create the following table to manage investigative cases. I typically use Studio to create custom tables.

  1. Label: Investigative Case
  2. Extends table: Task
  3. Add module to menu: Create New
  4. New menu name: Scooby Do Case Management
  5. Controls tab:
    1. Auto-number: Checked
    2. Prefix: ICASE
    3. Number: 1000
    4. Number of digits: 7
    5. Leave the default created User role
  6. Application Access tab: Leave default values
  7. Columns tab: No new fields – this table uses the following task fields (feel free to configure your form to show only these fields and create a dictionary override for the Number field to override Read only and make it read only):
    1. Number
    2. State
    3. Active
    4. Assigned to
    5. Short Description
    6. Description
    7. Comments and Work notes
  8. Click “Submit” to create the table.

3. Create a custom data migration tracker table to track the data migration process

  1. Label: Scooby Do Data Migration Tracker
  2. Extends table: Leave blank
  3. Add module to menu: Scooby Do Case Management
  4. Controls tab:
    1. Auto-number: Checked
    2. Prefix: DMT
    3. Number: 1000
    4. Number of digits: 7
    5. Leave the default created User role
  5. Application Access tab: Leave default values
  6. Columns tab: Add these fields (they allow data migration tracking):
    1. Column label, Column name, Field Type
      1. Active: u_active, True/False, default value: true
      2. Imported Data Sys ID: u_imported_data_sys_id, String, max length: 32
      3. Imported Data Number: u_imported_data_number, String, max length: 150
      4. Investigative Case: u_investigative_case, Reference (reference table: Investigative Case table created above)
      5. Investigative Case Completed: u_investigative_case_completed, True/False, default value: false

NOTE: Ensure the list view for the Scooby Do Data Migration Tracker includes all the above fields

4. Create a Data Migration Process Admin user specifically for doing Scooby Do data migration

  1. Add a new record in the sys_user table (NOTE: remember to elevate your roles to security_admin before doing this so you can assign this user the admin role)
    1. User ID: ScoobyDoProcessAdmin
    2. First name: ScoobyDo
    3. Last name: ProcessAdmin
    4. Active: checked
    5. Leave all other fields blank
  2. Create a new Group called Scooby Do Process Admins
  3. Add the ‘admin‘ role to the Scooby Do Process Admins group
  4. Add the ScoobyDo ProcessAdmin user to Scooby Do Process Admins group

5. Get the external data (xlsx) into ServiceNow. This will be the data that we migrate into the Investigative Case table.

  1. Download this sample xlsx file >> (ScoobyDoImportData.xlsx)
    1. If you don’t want to download the file, then create an XLSX file with the following column headers in row 1 and name the file ScoobyDoImportData.xlsx
      1. Case Number (ensure values are CASE#### starting with CASE0001)
      2. Status
      3. Title
      4. Description
      5. Case Notes
    2. Then fill in several rows with data –
      1. Ensure the Case Numbers have this format: CASE####
        1. Ensure the numbers are sequential starting with 0001
      2. Ensure the Status field uses one of 3 statuses:
        1. New
        2. WIP
        3. Done
  2. Import the data:
    1. Filter navigator: System Import Set > Load Data
    2. Import Set Table: Select “Create table”
    3. Label: Scooby Do Import Data
    4. Source of the import: Select “File”
    5. Upload the file: ScoobyDoImportData.xlsx
    6. Sheet number: 1
    7. Header row: 1
    8. Click “Submit” button
    9. At “Next Steps…” click “Loaded Data” and click some of the (i) info icons to preview the records
    10. Get the name of the import set table that was created above by going back to Studio to see the table and get its name. You’ll need this to perform next steps.

6. Populate the custom data migration tracker with the imported data sys_ids and number

I use a Scheduled Script Execution to get each imported data row’s sys_id and number from the import set table to add a row in the data migration tracker table. Let’s create a new scheduled script execution:

  1. Filter navigator: sysauto_script.list or in Studio Create Application File > Server Development > Scheduled Script Execution.
  2. Click “New”
    1. Name: ScoobyDo – Get Imported Data into Data Migration Tracker
    2. Run: On Demand
    3. Run this script: You can download the text of this code here.
      1. In line 4 change the importedDataTableName variable to the name of the table that was created when you completed the Load Data process above.
      2. In line 9 change the scoobyDoDataMigrationTrackerTable variable value to the table name from when you created the data migration tracker table earlier.
    4. Save the scheduled script execution.
  3. NOTE: The sysauto_script form doesn’t show the “Run as” field out of the box so we need to add it. (Ensure you follow your own company process for maintaining changes to global tables to promote those changes to other instances). We’ll need to change the application scope to Global for this, then add the Run as field to the form however you normally manage form fields.
    1. Add the “Run as” field beneath the Name field and save.
  4. NOTE: GO BACK TO the Scooby Do Case Management application scope before proceeding.
  5. Reopen the ScoobyDo – Get Imported Data into Data Migration Tracker scheduled script execution.
    1. In the “Run as” field, Select the “ScoobyDo ProcessAdmin” user and save the scheduled script execution.
  6. Review the script to see what it’s doing, then run the script by clicking the “Execute Now” button.
  7. Verify the script ran, by opening the Scooby Do Data Migration Tracker table list view
    1. Filter navigator: Scooby Do Case Management > click on Scooby Do Data Migration Trackers to open the list view
      1. IMPORTANT: sort (descending) the list view on the Imported Data Sys ID field. This is to ensure the list view matches how the data migration code gets the tracker rows which makes it possible to see at which record the data migration process broke).
    2. Ensure there is one record for each of the imported data rows and where:
      1. Active: true
      2. Investigative Case: is empty
      3. Investigative Case Completed: false
      4. Imported Data Sys ID: contains the sys_id from the imported data record
      5. Imported Data Number: contains the CASE#### number from the imported data record

7. Get data from the tracker table to create ServiceNow records (this creates Scooby Do Investigative Case records from the imported data)

  1. Note there is an intentional bug on line 14 of the code you will download. This is so you can see how the tracker allows you to determine where the process breaks. There are steps later to correct that bug then restart the process so you can see how easy it is to restart your data migration process from the point at which it broke.
  2. Create a new Scheduled Script Execution (sysauto_script.list > New or in Studio Create Application File > Server Development > Scheduled Script Execution):
    1. Name: ScoobyDo – Create Cases from Data Migration Trackers
    2. Run as: ScoobyDo ProcessAdmin
    3. Active: checked
    4. Run: On Demand
    5. Run this script: Download the text of this code here and copy the entire file to this field.
      1. NOTE: There are 3 variables where the values need to be changed.
        1. Line 1: importedDataTableName – change this value to the table name of the table that was created when you did the Load Data process to imported the XLSX file
        2. Line 2: scoobyDoRecordTable – change this value to the table name of the table you created for the Investigative Cases.
        3. Line 3: scoobyDoDataMigrationTrackerTable – change this value to the table name of the table you created above for the data migration tracker.
    6. Save the script.
    7. Review the code to see what it’s doing, then run the script > Click the “Execute Now” button.
  3. The data migration process should break when the imported data reaches the row with CASE0004 is reached.
    1. You can verify this by opening the Scooby Do Data Migration Tracker table list view
      1. Filter navigator: Scooby Do Case Management > Scooby Do Data Migration Trackers
      2. Sort on “Imported Data Sys ID” column – descending.
      3. NOTE that the “Investigative Case” column shows an ICASE####### value for 3 rows.
      4. NOTE that the “Investigative Case Completed” column shows true for the same 3 rows.
    2. Review this list view to see the first row where the Investigative Case Completed value remained ‘false.’ This is the row that broke during the data migration process.
    3. Well that’s great Rick, but how do I fix this?
    4. Let’s update the ScoobyDo – Create Cases from Data Migration Trackers scheduled script execution’s code by commenting out line 14 and then saving the code.
    5. Now that the bug is fixed, all we need to do is click the EXECUTE NOW button.
    6. What happens is that the code will only get rows from the tracker table where the Investigative Case Completed field’s value is false. Because the first 3 rows completed successfully, that value was updated to true. So now the process will begin with the first tracker row that didn’t work.
    7. Go back to the Scooby Do Data Migration Tracker and refresh the list view — then verify that all rows now have an Investigative Case link and Investigative Case Completed is true.

8. Verify that the created ServiceNow records match the imported data

  1. Verify that the data migration ran.
    1. Open the Scooby Do Data Migration Tracker
  2. Verify that the Investigative Cases were created and have the correct data.
    1. Stay on the Scooby Do Data Migration Tracker list view
    2. Click on each row’s “Investigative Case” number- they should be a link to the created case.
    3. Review the case against the imported xlsx file for accuracy
    4. Review the case’s Activity Stream to see that a work note was added that details from what imported case this was created.

And that’s it folks

That’s the entire process. While this is a pretty straightforward example, you can use this as a baseline for any data migration project you have.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.