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:

image

Just a plain schema with three elements, all required.

The output schema:

image

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:

image

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:

image

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:

image

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:

image

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