Can we stop Data Sources from creating columns?

[Travis28]

Is there a way to stop ServiceNow from automatically creating columns when someone clicks "Test Load 20 Records" or "Load All Records"? 

Doesn't sound like a good idea, I know, but hear me out. 

In non-production, this automatic creation is a huge benefit. Saves time having to create columns on the import table and prepares you for creating your transform maps. 100%, keep it here!

However, production is a different story. We have had several instances where people have uploaded files into a data source and ServiceNow creates a bunch of bad columns. This is typically where the CSV (mostly CSV in our case) is in the wrong encoding. If we receive the import file from someone else, it's an easy thing to miss, and unless you are familiar with this problem, you don't even look for it. This incorrect encoding creates columns with unreadable symbols, making us unable to open the tables and review the data. Then someone has to manually delete the bad columns to put us back in order again.

I would love if there were some option where the automatic column creation would not take place in our production environment. It's a stretch, for sure, but we can't be the only customer who runs into this problem, right?

Thanks!

[Marcos Kassak]

Hey Travis,

Got a long discussion with the recent customer because each file they sent to us, had different columns and that was creating new columns into a temporary table, so we figured out a process - we had a meeting with the end-user to define a template and they can download it on the Catalog Item prior to requesting an Import, then the Support Team can easily download that pre-defined template from the RITM with the columns that we already expect to be imported to target table.

That helped a lot, and end-users didn't have trouble because the columns were made based on their 'language' let's say... also the scripting behind was doing all the mapping.

Hope it helps in some way.

[Sulabh Garg · Accepted solution]

Hi Travis,

I can understand your use case and you are not the only one who faces this situation. 

Please use the below import set system property to restrict column creation on production.

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

For more details please follow the docs link

https://docs.servicenow.com/bundle/quebec-platform-administration/page/administer/import-sets/concep...

Hope it helps.

[Travis28]

This is beautiful, and exactly what I was hoping for. Thank you, Sulabh!