Transform map issue

E_19 · ‎05-12-2017

Hi All,

I have created a transform map to load users into sys_user table. The handoff file has 8000 users when I run the transform it only loads 2100 users for the remaining 5900 records I get:

Error during insert of sys_user (Brad Pitt) for each user record with unique user name.

I checked staging table field size and load data but see nothing unacceptable.

I have deselected the business rule flag to see if that makes a difference but no.

Anyone have any idea what might be causing this? I realise this is very vague but maybe there are some thought someone could share.

Many thanks,

Ellie

Alikutty A · ‎05-12-2017

Can you remove all the data and reload in once again with user id as the coalesce?

If you want to delete users, you can run this script as a background script. The script deletes all users created today in less time.

var usr = new GlideRecord('sys_user');

user.addEncodedQuery('sys_created_onONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)');

user.deleteMultiple();

Thank You

Please Hit Like, Helpful or Correct depending on the impact of response

View solution in original post

Alikutty A · ‎05-12-2017

Hi Ellie,

What is coalesce on your transform map? Have you checked the transform history or import logs for further clarity on the error?

Thank You

Please Hit Like, Helpful or Correct depending on the impact of response

Alikutty A · ‎05-12-2017

Hello Ellie,

Is your issue resolved by checking the logs?

Thank You

Please Hit Like, Helpful or Correct depending on the impact of response

E_19 · ‎05-12-2017

Hi Alikutty,

Thanks for the pointer I have done some digging and found

Unique Key violation detected by database (Duplicate entry '2498f68e4f467e40a1e260fe0310c763' for key 'PRIMARY')

but the transform map only coalesces on User ID which is unique and the duplicate entry I get for each record where the user has the same last name as an earlier loaded user and there are quite a few but that shouldn't even be considered unless there is something OOB happening I am not aware of.

I did another load and now found that regardless some records where no names match etc also get Duplicate entry error sample below

EXTRACT

user id sys id but this is a different user with user id K002105

1, 'K003922', 'faculty', 0, 0, 1, 'Name', '2017-05-12 11:54:04', '/', 'sys_user', 0, 2, '1378be4e4f467e40a1e260fe0310c71d', 'ec', 0, '2017-05-12 11:54:04', 'global', 'Fname Lname', 0, 'Lname', 'ec', 'fname.lname@xyz.ch', 0) /* company001, gs:SYSTEM, tx:d798ed374f6af600a1e260fe0310c7b5 */

So in short a record is inserted and gets the unique sys id then on a following record insert it errors out with duplicate entry referring to the same sys Id?

I am baffled. Any suggestions welcome.

Thanks,

Ellie

Alikutty A · ‎05-12-2017

Can you check if there any Data policies added on the sys_user table on last or first name of user?

Also please verify if the unique field is checked on any of the user table columns. The unique field is not available on the form or list layout but you will need to add it on the list layout for verification.

Thank You

Please Hit Like, Helpful or Correct depending on the impact of response