question

AlexPeake-8006 avatar image
0 Votes"
AlexPeake-8006 asked SaurabhSharma-msft commented

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

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)?

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@alexpeake-8006 Thanks for using Microsoft Q&A !!

I have tried importing the Time column with same value - 14:31:59 however it is not appearing as 0:14:31:59.0000000 in the destination file.
Source:
82512-image.png

I am seeing 14:31:59 in the csv destination file in Blob Storage.

Destination:
82484-image.png

Are you doing schema mappings in ADF ? Could you please provide more details if you are doing anything different?
82521-image.png


0 Votes 0 ·
image.png (37.6 KiB)
image.png (17.9 KiB)
image.png (15.4 KiB)
AlexPeake-3372 avatar image
0 Votes"
AlexPeake-3372 answered SaurabhSharma-msft converted comment to answer

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:

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I ran this on sql server (management studio):
select top 10 TIMESBM from dbo.CATClaimHistory;

And got:
TIMESBM
16:38:39
17:11:00
16:24:01
18:38:53
18:38:41
08:14:30
16:45:35
18:34:52
17:27:41
19:37:51

I created a copyData in ADF, source as above, query as above. Sink is a delimited csv with gzipped output. Dezipped result is:
$ cat data_cadb43ec-0a4e-4683-8bce-40e2bd78e0e6_3e32ea80-dfd2-4ac9-aa4d-d93e4365055f.csv
0:16:38:39.0000000
0:17:11:00.0000000
0:16:24:01.0000000
0:18:38:53.0000000
0:18:38:41.0000000
0:08:14:30.0000000
0:16:45:35.0000000
0:18:34:52.0000000
0:17:27:41.0000000
0:19:37:51.0000000

What else can I do?

0 Votes 0 ·

@AlexPeake-8006 ok, got it. I am looking into this and get back to you.

0 Votes 0 ·

@alexpeake-8006 I am still trying to repro this issue and it is working fine for me

83710-image.png

I have tried both SQL on premise and Azure SQL database with collation - SQL_Latin1_General_CP1_CI_AS. Also, with the time datatype.
83719-image.png

Did you set anything up for Time Span format in ADF ?

83725-image.png


0 Votes 0 ·
image.png (51.0 KiB)
image.png (112.0 KiB)
image.png (28.8 KiB)
Show more comments
SaurabhSharma-msft avatar image
1 Vote"
SaurabhSharma-msft answered

@alexpeake-3372 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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexPeake-3372 avatar image
0 Votes"
AlexPeake-3372 answered SaurabhSharma-msft commented

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:
84107-image.png



image.png (12.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@alexpeake-3372 Interesting. Really appreciate if you could you please download and share Support Files (using the steps below) as a Zip file over an email to azcommunity[at]microsoft[dot]com with this Q&A thread link and subject as "Attn: Saurabh' so that I can share this with the products team as well.
84153-image.png


0 Votes 0 ·
image.png (38.2 KiB)
AlexPeake-3372 avatar image
0 Votes"
AlexPeake-3372 answered SaurabhSharma-msft commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@alexpeake-3372 Awesome. Great to hear that. Thank you.


Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

0 Votes 0 ·