More Information on UpdateGrams

In previous blog posts I have mentioned this large project that I am working on.  This project has been a great project for finding lots of material to blog about.  What I have not mentioned so far are the people that I am working with. 

This project is a joint effort between Microsoft Consulting Services and Microsoft Global Services India (MGSI).  There have been great contributions by all of the off-shore team (unfortunately, far to many to list) and I would recommend them anytime as an off-shore group if you are looking to implement an off-shore model. 

I have been very fortunate to be working closely with a small tight knit sub team.  This team is made of up Vishal Agrawal, Ashish Mehndi, Ashish Shukla and Benny Matthew.

Why do I bring this up?  Well, in the last blog post I talked about changing the config file so that we could validate our canonical messages.  Once we did that we quickly found out that all of our interchanges that utilized a SQL UpdateGram broke.  They broke because the map that generates the UpdateGram uses a character to represent the parent/child relationship.  This character gets mapped to an element that is an Integer type - which is were it no longer conforms to the schema.  We did this because when the UpdateGram was sent through the Send Port the SQL Adapter uses SQLXML which takes that character data and replaces it with the value of the parents primary key.

So, the first thing that we did was to modify the map to use a numeric value (yes, numeric values can be used) and we decided that we were going to use the following scheme so that we would not overlap with real data.   We used a script functoid with the following code

System.Int32.MinValue.ToString() + the value from the iteration functoid.

Why do I bring up the issue with overlapping data?  Well, that brings us to the second interesting (and important) fact about using the UpdateGram. 

How does the SQLXml functionality work internally when replacing the child key with the value from the parent?  Well, it takes the identifier (lets say that we were still using a character and that the character was Y) and does a string replace.  The string replace however is done across all of the data in the child record and not just the child foreign key column.  We found very quickly that all of our columns that contained a Y were now replaced with the value from the parent key relationship - definitely not what we wanted!  This only happens if the data in the field is only a Y.  This does not happen if there is a Y in the middle of a word.

So, if you are still going to use character data to identify your relationships in the UpdateGram make sure that you take this into account.