Time format error ADF Pipeline copyData from MS SQL Server Time column

Question

I am using ADF pipeline copyData to extract from MS SQL Server to a CSV. Several columns in this table are defined as data type Time. Looking in the database, a time of for example, 14:31:59 appears in the CSV as 0:14:31:59.0000000

Notice the leading 0:

I believe that this is an error. Noticed because I cannot import into the data warehouse. There is no custom treatment of the data, no mapping, just a simple copy.

Is there a fix? Is there an obvious way to deal with this (other than chop off the leading 0: -- which will break if it is fixed)?

Answer 1

@Alex Peake Have you created this pipeline this year or is this an old pipeline ? Last year, we had a big improvement on our type system in copy activity (including the format handling). One of them is to change the default TimeSpan format from The General Long Format (what you are seeing) to The Constant Format to align with .NET framework’s default format. As it’s a behavior change, the improvement didn’t apply to pipelines created before the improvement.

If it is a new pipeline could you please share the runid of your of your copy activity to look into backend logs.
Also, regarding the above screen, Type Conversion settings are collapsed by default. You can expand it to see additional properties.

Answer 2

I am doing no mapping. Just a select from sql server, and a copyData to a gzipped csv. All times come out in this format with the leading 0:

Answer 3

This is a pipeline that I just created for this test. Runid 31b2aeb0-e39f-46f0-b7b4-3bc5668f5bc4

The Type Conversion, I saw was collapsible. However, I do not have the entry to un-collapse. There is nothing there:

Answer 4

Problem solved! Although it was a new pipeline, I had copy and pasted a CopyData from a few months ago. When I created a new CopyData, it all worked as expected. Also I got the Type Conversion choices (which I left at default).

Thanks for you help on this.