Prevent insert of record(s) in table during data load

[Mo27]

Hi there,

I have an issue where during data load using an excel doc to a staging table, if additional columns/miss spent columns are included within excel doc, it will create a new system dictionary entry and column within the staging table. I wish to restrict certain users from being able to do this during a data load. 

I have checked the ACL - create and write on sys_dictionary and its able to bypass the required role(s). 

Is it possible to a create a business rule to run before insert on sys_dictionary to check if the user has a certain role and also, passed the write ACL on sys_dictionary to insert the data in the table? If so, how is this possible? 

Please can you provide me with examples if you have any or any articles.

Thank you 

[premer]

Hello,

Not sure about using the ACLs, but if you are using a transform map, you could write an onBefore transform script to prevent insertion of new records:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here
	if (action == 'insert') 
  ignore = true;

})(source, map, log, target);

[Mo27]

This is before the data is transformed into the table. It’s during when you load data into the staging table. If you have additional columns in the load, it will create new system definition columns and I need a way to prevent this Thanks

[Michael Kaufman · Accepted solution]

It's a global setting, but you could turn it off temporarily when you want to do a new import:

System Property: glide.import_set_row.dynamically_add_fields

Specifies whether an import set can add new columns to the staging table (true) or not (false). Instances that contain large numbers of import sets can sometimes become unresponsive when an import adds a column because the instance must alter every row in the staging table. In some cases, the database alter table action causes an outage. Setting this property to false prevents an import set from adding columns to the staging table and produces a log message. As a workaround, administrators can manually add a column to the staging table by creating a new dictionary entry and then reimporting the import set.

• Type: true | false
• Default value: true
• Location: Add the property

https://docs.servicenow.com/bundle/newyork-platform-administration/page/administer/import-sets/refer...

[Mo27]

Hello,

Thank you this has worked however, I require certain roles still to be able to dynamically create new columns during data load. 

I have attempted to write a business rule before insert on sys_dictionary to query the sys property and set the value to false/true depending on roles the user holds... However, I have a feeling that workflow is set to false during import as the business rule is not executing during this load (tested with sys log messages and script). 

Are you aware if this is the case? 

Thank you