Hi all
Today, a former customer of mine called me and had a question. She is using updategrams to insert rows into a table in SQL Server, and she had a problem with dates. The input CSV sometimes had an empty string for a date.
This would translate in the XML to an element with an empty string inside it. This would then get mapped to an attribute for that column in the table, and when SQL Server read the updategram, the 01-01-1900 was inserted into the date field because no other value was specified.
The customer wants null to be inserted instead. She asked me how to do this, and I said that that was easy - I would send her an example later this night.
So I started preparing my example, and it turns out, that my approach only works for elements and records - I have never before had to suppress an attribute, so I wasn't aware of this. My approach being, bye the way, that I connect a logical functoid to the destination node that I don't want created under some circumstances. But a logical functoid can not be connected to an attribute in the destination schema. Bummer.
So I eventually went with a custom scripting functoid, and my example ended up like this:
Input schema:
Just a plain schema with three elements, all required.
The output schema:
This schema has three attributes to simulate the updategram and also an element just to show the approach for elements.
The map ended up like this:
Element1 goes to att1 and Element3 goes to att3. No magic there.
As for element2 in the output, I have used to functoids to get it to be created only when a string is present in the source. The first functoid (the red one) is a "String Size" functoid, which returns the length of a string. The green functoid is a "Not equal to" functoid, which has the string length as one parameter and the constant "0" as the other parameter. The order of these parameters do not matter. So basically, at runtime, the element2 element is created if and only if, the length of the string in Element2 is larger than 0. For this particular customer, I could have used the "Logical Date" functoid instead of the combination of these two functoids, since that will return true if the input string is a date, which is exactly what I want. But the above solution is more general and applies to a lot more scenarios. Downside being, of course, that the above solution will accept a "abc" as a string, and since the length is larger than 0, it will get to the output.
Anyway, the customer didn't have elements, but attributes. for this I had to use a custom scripting functoid with this code:
Basically, this call template takes one parameter, being the element in the source that might be empty. It then check the value to see if it is different from the empty string, and if it is not the empty string, an attribute is created and the value for the attribute is set to the input parameter.
Now, as a side node, the input my customer has is a CSV file, so actually, in this case you can also take advantage of a nice little property on the schema. To demonstrate, I have created a flat file schema for a CSV input:
As you can see in the properties window, I have set "Suppress Empty Nodes" to "Yes". When BizTalk parses the flat file, the nodes that would be created as empty nodes now aren't created at all. This means, that the map can just look like this:
No functoids required, and it will give the exact same output as the above map. Do notice, that this only works with flat files where you make BizTalk suppress empty nodes.
So, that was all... feel free to ask questions :-)
You can find my demonstration project here
I hope this turns out to be helpful to someone.
--
eliasen