Data imports from email attachments

[Brian Broadhurs]

I'm trying to find a way to import data from an email attachment - this is a status update from an external supplier of the change requests that have been passed to them, and it could arrive at any time, not necessarily according to a regular schedule. My current thinking is to use an inbound email action to copy the attachment to a Data Source (sys_data_source) record, then trigger a data import using that data source. I've come across a number of issues:

1) I can't get the inbound email action to copy the attachment to the data source record. I've used the wiki entries at http://wiki.service-now.com/index.php?title=Update_Incidents_with_Attachments and http://wiki.service-now.com/index.php?title=Copy_Attachments_from_Record_to_Record as examples, but it just doesn't seem to work.

2) Once the email attachment is attached to the data source, how do I trigger the data load and transform? I could use a scheduled import to run periodically, but there might not be anything to process - I guess that might not be a problem, but is it possible for the pre-import script to abort the import based on the value of a field in the data source record that I can set in the inbound action?

3) Does a scheduled import also run the transform? If not, how can I automatically trigger that?

4) Once the import and transform is complete, I will need to remove the attachment from the data source, otherwise the number of attachments will keep rising - and presumably will get re-processed every time the import runs.

Is there a simpler way to do this?

Brian Broadhurst

[marcguy · Accepted solution]

Hi Brian, this is from another post, so not my script and you may have already tried this:

This is an inbound email action on the sys_data_source table. Assumes the import set, import set map and import set schedule are all set up. The import set schedule needs to be way off in the future so it never runs - well not in our lifetime anyway !!

// set up the data source. The system will automatically add the attachment for us.
gs.log ('TDI001 - Setting up data source');
current.name = "test director import" + gs.nowDateTime(); //append date time to name of data source for audit
current.import_set_table_name = "u_attachment_test";
current.import_set_yable_label = "attachment test";
current.type= "File"
current.format = "CSV";
current.file_retrieval_method = "Attachment";

var myNewDataSource = current.insert();

gs.lg ('TD002 - Data source inserted with sys_id - ' + myNewDataSource);

//point the scheduled import record to the new data source

var gr2 = new GlideRecord ('scheduled_import_set');

gr2.addQuery('sys_id', '12c4a37b4a3623280013c25cca62b284'); //need to remove sys_id reference. need lookup based on name perhaps?
gr2.query();

if (gr2.next()) {
gs.log ('TDI003 - Found Scheduled Import definition');
gr2.data_source = myNewDataSource;
gr2.update();
gs.log ('TDI004 - Scheduled Import definition updated to point to data source just added');

Packages.com.snc.automation.TriggerSynchronizer.executeNow(gr2);

} else {
// add error conditions to email somebody that this has occurred
gs.log('TDI005 - ERROR - Unable to locate scheduled import definition. Please contact your system administrator');
}

gs.log ('TDI007 - Inbound email processing complete');

[Brian Broadhurs]

Thanks Marc, I'll give that a try.

As a matter of interest, is there anywhere that describes the functionality such as TriggerSynchronizer? I'm sure that such functionality would be useful in a number of circumstances.

Brian

[Brian Broadhurs]

Marc,

that works a treat - thanks for that.

Brian

[cabry]

Thanks for the useful code snippet. This helped me get to where I needed to go.

One note though. As of Calgary,

SncTriggerSynchronizer replaces Packages.com.snc.automation.TriggerSynchronizer

So the code

Packages.com.snc.automation.TriggerSynchronizer.executeNow(gr2);

needs to be replaced with

SncTriggerSynchronizer.executeNow(gr2);