Azure Data Factory: Date formats when working with XML in Data Flows

31 March 2021

If there was one thing you could guarantee would always go wrong when importing data it's date formats. Some countries like to show dates as dd/MM/yyyy others prefer MM/dd/yyyy and despite formats like yyyy-MM-dd being a thing in programming for a very long time, people seem to still create files with these ambiguous formats.

I found finding out how to specify what format the date is in within a flat file was not obvious. If you go to the dataset item for a XML file, unlike other formats it's missing the schema tab.

This wasn't overly surprising as I've come to find when using Datasets in a Mapping Data Flow half the time it seems to ignore any schema definition, or if you use wildcard paths it even seems to ignore all the path settings on the dataset.

Within the Data Flow, the source has a projection tab which will import a schema from your xml file. If your data looks like a date, then this will hopefully set the data type to date. One thing I found was having data in dd/MM/yyyy resulted in a format of string rather than date. Annoyingly unless you want to start manually editing the script the UI generates, there's no way of fixing the projection.

Assuming you have a date as the data type this is a good start, but I then found when I ran my data flow which had dates in dd/MM/yyyy, the date field was blank! So it definitely knows it's a date and not a string, but it's doesn't like the format so it's ignored the data.

Back on the projection tab of the source there is another button "Define default format". This will open a side panel where you can set what format your dates, times, whole numbers and fractions will be in. Once I had set this, my dates started feeding through.

How to create a custom droplist in Sitecore

4 October 2020

You could call this how to create a custom drop down, or select list, potentially even picker, but if you've ended up here hopefully what your after is a way to create a drop down list in the Sitecore admin populated with options that aren't from Sitecore.

Fortunately this is quite simple to do by creating a new field type. First we will create the code are new custom field type, then add it to the list of field types in the core db and then use it in a template.

Custom Field Type Code

Either create a new class library or decide on an existing one to put the code for your new field type in.
I've imaginatively gone for one called HiMyNameIsTim.Sitecore.FieldTypes
Add a reference to Sitecore.Kernel (you can get this from the Sitecore NuGet feed)
Create a class for your custom field type
Add the following code, replacing the options with your logic to get the values.

1using Sitecore.Web.UI.HtmlControls;
2
3namespace HiMyNameIsTim.Sitecore.FieldTypes
4{
5    public class SchemeDropList : Control
6    {
7        protected override void DoRender(System.Web.UI.HtmlTextWriter output)
8        {
9            output.Write("<select" + ControlAttributes + ">");
10            output.Write("<option></option>");
11
12            string s;
13            if (base.Value == "1")
14                s = "selected";
15            else
16                s = "";
17            output.Write("<option value=\"1\" " + s + ">First</option>");
18
19            if (base.Value == "2")
20                s = "selected";
21            else
22                s = "";
23            output.Write("<option value=\"2\" " + s + ">Second</option>");
24            
25            output.Write("</select>");
26
27            RenderChildren(output);
28        }
29    }
30}

Publish this into your site
Login to the admin and switch to the core db
Navigate to "/sitecore/system/Field types/List Types/" in the tree
Create a new item of type "/sitecore/templates/System/Templates/Template field type"
Populate the assembly and class option with the values for the class you just made. For me this is:

Switch to the master DB and create a template using your new field type. Here's mine:

Create a page and you should have a drop down list with the values from your class.