none
ADFV2 Conversion issue when loading data from On-Prem to Azure data warehouse Gen 2

    Question

  • Hi Everyone,

    I am experiencing conversion issue while loading from on-prem server to azure datawarehouse gen2. Load process is also staging the data first to blob storage and then load data to ADWH Gen2. I checked and all schema between source on-prem server and AZDWH is same but still it's giving error- Conversion failed when converting the NVARCHAR value to data type DATETIME2. Any help would be appreciated.

    Complete error:

    Activity CopyData failed: ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed. (/97a7e203-6800-41e4-a516-7931b16bca03/Polybase/data_97a7e203-6800-41e4-a516-7931b16bca03_4b060930-9bdb-4736-b69f-2e6cc410f6fd.txt)Column ordinal: 1, Expected data type: DATETIME2(7), Offending value: FFC65484-43AE-4805-A9A2-477B9327F6CE (Column Conversion Error), Error: Conversion failed when converting the NVARCHAR value 'FFC65484-43AE-4805-A9A2-477B9327F6CE' to data type DATETIME2. ,Source=.Net SqlClient Data Provider,SqlErrorNumber=107090,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=107090,State=1,Message=Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed.

    Friday, July 13, 2018 4:18 PM

Answers

  • Hi, 

    Can you try specify the SQL query as a SELECT statement with a full list of column names, e.g. SELECT column1, column2, ... FROM mytable , instead of SELECT * ?

    Saturday, July 14, 2018 6:02 AM

All replies

  • Hi, 

    Can you try specify the SQL query as a SELECT statement with a full list of column names, e.g. SELECT column1, column2, ... FROM mytable , instead of SELECT * ?

    Saturday, July 14, 2018 6:02 AM
  • Thanks for your response. This worked when specified individual column names. BTW, Since all columns are same in both source and destination, Any reason of not using Select * but using Select col1, col2...?
    Monday, July 16, 2018 7:59 PM
  • This is because the column order of the result of SELECT * isn't necessarily the same as the column order in the table definition. Hence the data transferred into the staging Azure Blob could be in a different column order than what Polybase would think.
    Tuesday, July 17, 2018 4:48 AM