When trying to process an XML Collection Table, I get an error saying: “The given value of type String from the data source cannot be converted to type datetime of the specified target column.” What could cause this?
Check the input file(s). The datetime columns cannot have empty elements. The empty elements are read as empty strings, which in turn is not a valid datetime format.
If this is the case, either change the datetime column(s) to nvarchar data type (and optionally convert to datetime in SQL afterwards) or use an XSLT Transform to remove the empty datetime elements (they will then become NULL, which is valid).