Importing Attachments from another ServiceNow instance

General Add comments
by:

A couple of weeks ago I had to migrate data which contained many attachments from one ServiceNow instance to another ServiceNow instance. I found out that it was not as straight forward as I expected it to be. Most of the information is available on the ServiceNow Wiki and ServiceNow community, but it took me some time to gather it all and to transform the information into a working import. I’m sharing my experience here with you and if you know of have a better way to do this, please let me know.

How attachments are stored

First some background information about attachments in ServiceNow. You can find a clear description of how ServiceNow stores attachments in the database in the ServiceNow Wiki article “Administering Attachments”:
When you store an attachment to any table, a record is created in the sys_attachment table that contains attachment metadata, and the file data is stored in the sys_attachment_doc table, in 4k chunks. For example, if you attach a 12k file called My_attachment.pdf, then you would have a sys_attachment entry with 3 related sys_attachment_doc entries.

The relation between the record in any table and its attachment is created with a Document type reference. The two fields table_name and table_sys_id in the attachment table work together to reference the record in any table, for example a company in the company table. See the ServiceNow Wiki article “Creating a Document ID Field” and our SNC-Blog article “Enable use of variables in Change Task” for more information. The relation between the attachment metadata and the file data is created with a standard one-to-many reference.

You should also take note of when you store an image to a field in any table, a record is created in the sys_attachment table, and in the sys_attachment_doc table in almost the same way as with attachments. The difference is visible in the fields table_name and file_name. For attachments:

  • The table_name contains the name of the table were the record is stored for which this is an attachment.
  • The file_name contains the name of the file that is attached to that record.

For example a MS word document “ECMA Service Contract 2012.docx” attached to a service contract, creates an attachment with table_name “ast_service” and file_name “ECMA Service Contract 2012.docx”.

For images stored to a field:

  • The table_name contains the name of the table were the record is stored for which this is an attachment with the prefix “ZZ_YY”.
  • The file_name contains the system name of the field to which the image is stored.

For example a company logo “ECMA.PNG” stored as a banner image to a company in the company table, creates an attachment with table_name “ZZ_YYcore_company” and file_name “banner_image”.

Database views

As advised in the ServiceNow Wiki article “Importing from another ServiceNow instance” I used an XML import set to import the data and attachments. I choose not to reconcile on the sys id field as described in the article, but to use database views instead. I am not sure if that was a good choice or not. Feel free to comment on this.

I created a database view for the sys_attachment and sys_attachment_doc table in which I joined the attachment tables with the data table so that I have the fields available to uniquely identify the records in the data table. See the examples below for exporting sales accounts with their attachments.

Image file

My experience with the import of attachments, for images stored to a field, is that the insert in the Attachment table must be directly followed by the insert of the corresponding data file record(s) in the Attachment Document table. When the time frame between both inserts is too long, ServiceNow does not create a .iix image file. ServiceNow uses this file to display the image on the form. I did not have time to debug this behavior or to figure out what the maximum time frame is, but I did not have the problem when I kept the time frame within 10 to 15 minutes.

For this .iix image file issue I separated the export of attachments for images stored to a field from the other attachments. Just to be sure that all attachment documents of one attachment metadata are in the same import set.

Exporting thresholds

I also break up the import of attachments to be able to work around the upper threshold limit of 50.000 for exporting XML data. See the ServiceNow Wiki article “Exporting Data” for more information. In this particular example, I was not able to reach the maximum of 50.000 records. At best I could import no more than 15.000 records per import run. This can be caused by the development instance that I used as my target ServiceNow instance. It is most likely a memory issue.

Data Source and Transform Map

See the ServiceNow Wiki article “Importing from another ServiceNow instance” for information on how to create the data source and transform map.

The examples below show the file paths I used to import the data from the source ServiceNow instance:

  • File path import Sales Accounts (+/- 1500)

/u_exp_sfa_account.do?XML

  • File path import Sales Account Attachments (+/- 400)

/u_exp_acc_attachment.do?XML

  • File path import Sales Account Attachment Documents (+/- 95.000, %3C equals <, %3D equals =, %3E equals >, %5E equals ^, see ASCI Table and Desciption).

File path for 1st Data load: /u_exp_acc_attachment_doc.do?XML&sysparm_order=doc_sys_id&sysparm_query=att_table_name%3DZZ_YYsfa_account&sysparm_record_count=15000

File path for 2nd Data load: /u_exp_acc_attachment_doc.do?XML&sysparm_order=doc_sys_id&sysparm_query=doc_sys_id%3C%3D2218f7fb4a3623070109c0c8ccf9dd80%5Eatt_table_name!%3DZZ_YYsfa_account&sysparm_record_count=15000

File path for 3rd Data load: /u_exp_acc_attachment_doc.do?XML&sysparm_order=doc_sys_id&sysparm_query=doc_sys_id%3E2218f7fb4a3623070109c0c8ccf9dd80%5Eatt_table_name!%3DZZ_YYsfa_account&sysparm_record_count=15000

File path for 4th Data load: /u_exp_acc_attachment_doc.do?XML&sysparm_order=doc_sys_id&sysparm_query=doc_sys_id%3E7da07f594a36230701dfad848d5b3b72%5Eatt_table_name!%3DZZ_YYsfa_account&sysparm_record_count=15000

File path for 5th Data load: /u_exp_acc_attachment_doc.do?XML&sysparm_order=doc_sys_id&sysparm_query=doc_sys_id%3Eafdb79b34a36230701691b3cc1692924%5Eatt_table_name!%3DZZ_YYsfa_account&sysparm_record_count=15000

File path for 6th Data load: /u_exp_acc_attachment_doc.do?XML&sysparm_order=doc_sys_id&sysparm_query=doc_sys_id%3Eb5e2a08a4657920201fa0184f7b00e67%5Eatt_table_name!%3DZZ_YYsfa_account&sysparm_record_count=15000

File path for 7th Data load: /u_exp_acc_attachment_doc.do?XML&sysparm_order=doc_sys_id&sysparm_query=doc_sys_id%3Ecc7060344a36230700e3a4e6c32d1900%5Eatt_table_name!%3DZZ_YYsfa_account&sysparm_record_count=15000

See the section “Breaking up large exports” in the ServiceNow Wiki article “Importing from another ServiceNow instance” for information about how to do this and on how to retrieve the correct next sys id. I used the connection URL from the data source for Attachment Documents to display the record list, by removing the “XML&” and adding “_list” before the “.do”. When you add the sys id as a column to the list and type 15.000 in the row number, you can, after you click next page, retrieve the sys id from the first row in the list.

In the transform maps I mapped the fields to the corresponding fields in the target instance and used the following source scripts to map the reference fields:

  • Sales Account Attachment
    • table_name

[cc lang=”javascript”]
answer = ‘core_company’;
if (source.u_att_file_name == ‘u_logo’) {
answer = ‘ZZ_YYcore_company’;
}
[/cc]

  • table_sys_id

[cc lang=”javascript”]
var gr = new GlideRecord(‘core_company’);
if(gr.get(“name”,source.u_acc_name)) {
answer = gr.sys_id;
}
[/cc]

  • file_name

[cc lang=”javascript”]
answer = source.u_att_file_name;
if (answer == ‘u_logo’) {
answer = ‘banner_image’;
}
[/cc]

  • Sales Account Attachment Document
    • Reference to sys_attachment

[cc lang=”javascript”]
answer = ”;
var grAcc = new GlideRecord(‘core_company’);
if (grAcc.get(‘name’, source.u_acc_name)) {

var gr = new GlideRecord(‘sys_attachment’);
gr.addQuery(‘table_sys_id’, grAcc.sys_id.toString());
gr.addQuery(‘content_type’, source.u_att_content_type);
if (source.u_att_file_name == ‘u_logo’) {
gr.addQuery(‘table_name’, ‘ZZ_YYcore_company’);
gr.addQuery(‘file_name’, ‘banner_image’);
} else {
gr.addQuery(‘table_name’, ‘core_company’);
gr.addQuery(‘file_name’, source.u_att_file_name);
}
gr.query();
if (gr.next()) {
answer = gr.sys_id.toString();
}
}
[/cc]

Questions

Everything works fine now, but I still have some questions. Was it a good choice not to use the sys id as the reconciliation key? Is there an easier way to do this without the upper threshold limit?

Later I found another ServiceNow Wiki article “Retrieving A Large Number Of Records From ServiceNow”. Does it provide a better alternative?

You can reach me at marco.coufreur@2e2.nl.

Leave a Reply