[sims] ATF Import failing with SQL violations

[Rammie]

Any constructive suggestions on how to resolve ATF import problems? The source is Tribal and I seem to recall problems with this in the past, although I can't seem to locate the thread. Specifically, I've already fixed a number of issues with contact phone numbers exceeding the field length and I've also located a number of duplicate parental mother records (PAM) attributed to different contacts. This has removed all of the explicit error reports except some final fatal SQL-related errors ('UNIQUE KEY' and a number of 'Cannot insert NULL' errors). I won't bore you with the detail unless someone thinks it's useful. Currently, we're trying to use SQL debugger to identify what data is triggering the error. PS - doesn't say much for validation, does it?

[matt40k]

Sounds like a duff export.

[wpjon]

We're a school in Derby too and had issues importing our ATF file, the main one being about a Unique Key, our SIMS support team passed our ATF file to Capita who said the contacts in the file were a mess. I ended up removing all the contact details and importing the ATF without them, we'll import contact details through CTF files which we will get from the primary schools.

[Rammie]

We've resolved it now. Thanks, wpjon, it's "reassuring" to discover someone else has experienced the same problem from (presumably) the same Tribal source. We had seriously considered dumping all the contact information, until the SQL debugging revealed the source of the problem. It looks like the SIMS contact pattern matching algorithm is sensitive to gender problems. Specifically, if it matches a contact on name etc and tries to update the record, the import will fall over if the gender doesn't match. Clearly the root cause is a discrepancy in the gender information somewhere or other (could be in the existing record or in the ATF file). Our fix is to remove all the contact gender information and we then successfully imported the file.