The most horribly boring post most of you will ever read

I've been sitting here listening to Har Mar Superstar, Loreena McKennitt, Prince and Fiona Apple and updating my blogroll and pondering whether I should subject anyone to this little gem. I don't post technical posts very often, particularly because although I'm a programmer, I don't contribute a lot to the field (to my company, absolutely, just not to the profession in the way of forum responses, books, etc.). Call it sheer laziness if you like, because I'm capable of it, I just choose to direct my energies elsewhere. So it is with great sadness that I actually force such an item upon you, something that I have failed to find spelled out precisely elsewhere on the web that should be dumped on the web so that various individuals might find it when it is necessary to their sanity.

How to Make Your One to Many to Many SQL Server Updategram Function Appropriately When You Have Secondary or Tertiary Fields Involving a Rank or Numeric Value and Are Using a Biztalk Iterator Functoid to Populate the Updategram Identities (also applicable to non-Biztalk/BTS strict updategram functionality when using positional values)

Issue: when I moved from a one to many relationship to a one to many to many structure in an XSD file, I had to add a secondary updategram auto-id (updg: - if you haven't gotten this far yet in your project, my post is useless to you - but you can send me an email if you're desparate) node that had to resolve many records in the secondary table to many records in the tertiary table. You can't just use a constant like you can for all the first one to manys, because you'd have the same constant for multiple one to many relationships that don't actually all share ids (usually we just map in an "x" or "id" constant and the keys in the sub tables get the same constant > in this case, you have to be able to map many distinct parent records to all their distinct children, so an "x" constant alone is no good - you need "y", "z", "ab", "ac", etc.). In the past I fixed this by just mapping the iteration number of the secondary table record to the updategram id and the sub table/tertiary relational id - so "1" to lots of "1"s, "2" to lots of "2"s, etc. However, once another iteration value (rank for instance) entered the picture there was a problem because that value has "1" or "2" or "3" in its field, effectively telling the in-memory updategram that the constant is a replaceable value for any identities returned from the transactional insertion. The end result is that the updategram tries to map back to that value as a constant linked to the updategram id and when it gets confused, simply nulls it. In most cases, you'd get an outright transactional failure with rollback, but in this case you'll see a very peculiar behavior, the field is just nulled - I don't know if SQL Server just decides better safe than sorry, but the insert proceeds and ignores the constant to which it doesn't feel confident mapping a value. The fix (in BTS) involves mapping the iterator functoid to a new scripting functoid in the MAP that takes the iterator int(eger) as a parameter and just appends "CPID", or any other constant you feel won't be replicated elsewhere, to the end so that it becomes something unique you won't see in the XML otherwise (you could probably use a string concatenation functoid somehow instead, but that was just being annoyingly useless in my tests because of the string data type restriction). If you have an XSL setup instead of Biztalk, the same rule applies, just use a function or template to append a string to the end of the iterator. You'll want to test it, obviously, but it seems to work like a charm. Little fix, big impact.

Note, if you have MULTIPLE many-to-many relationships in a single updategram, you'll want multiple functoids (XSL functions) each with a unique string appended to the iterator so that it can tell the relationships apart.

Further note, if you ever see something similar happening, even in a non one to many to many updategram (just one to many), check and make sure that there isn't a field whose value - complete value, not just a piece of it - isn't exactly the same as the constant used to map the relationship. i.e. if a column has the value "x" for instance (but not "xyz"), that would be a problem if the updg:at-identity and corresponding sub table's ID uses "x" as a constant.

Code in Biztalk Scripting Functoid:
public string AppendConstToInt(int param1)
{
return param1.ToString() + "CPID";
}