locked
Failure - FailedDbOperation - Microsoft.DataTransfer.Common.Shared.HybridDeliveryException RRS feed

  • Question

  • I am just getting started with a Azure Datawarehouse and I would like to use the Azure Data Factory to load the data from an Azure SQL Database to the DW in the same resource group.

    The simplest of scenarios I believe as I am using the autocreate feature to create the table in the DW. I can see a succesful connections, table gets created but no data is created as it fails with the following:

    Copy activity encountered a user error: 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=Conversion failed when converting from a character string to uniqueidentifier.,Source=.Net SqlClient Data Provider,SqlErrorNumber=8169,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=8169,State=2,Message=Conversion failed when converting from a character string to uniqueidentifier.,},],'.

    As I am creating the schema based on the source, I am guessing there is something else happening. How can I begin to diagnose as I do not see how to access the detail of the pipeline?   

    In case it is helpful:


    Cheers, Jeff

    Wednesday, April 11, 2018 11:13 PM

Answers

  • The specific error is: Conversion failed when converting from a character string to uniqueidentifier 

    I also see that it appears you are trying to load 'dbo' schema (select * from [dbo].[Users]).

    Here is a 'How-To' document that walks you through the process of migrating data from SQL Database to SQL Data Warehouse using Azure Data Factory: Load data into Azure SQL Data Warehouse by using Azure Data Factory

    The following could be used to verify and troubleshoot any schema issues before kicking things off with Azure Data Factory: Data Warehouse Migration Utility (Preview)

    Regards,

    Mike

    If this information satisfies your request, please mark this response as answered so that it assists others looking for the same information.


    Thursday, April 12, 2018 4:45 PM

All replies

  • The specific error is: Conversion failed when converting from a character string to uniqueidentifier 

    I also see that it appears you are trying to load 'dbo' schema (select * from [dbo].[Users]).

    Here is a 'How-To' document that walks you through the process of migrating data from SQL Database to SQL Data Warehouse using Azure Data Factory: Load data into Azure SQL Data Warehouse by using Azure Data Factory

    The following could be used to verify and troubleshoot any schema issues before kicking things off with Azure Data Factory: Data Warehouse Migration Utility (Preview)

    Regards,

    Mike

    If this information satisfies your request, please mark this response as answered so that it assists others looking for the same information.


    Thursday, April 12, 2018 4:45 PM
  • thanks Mike. I will look at those links. What did you mean by "I also see that it appears you are trying to load 'dbo' schema (select * from [dbo].[Users])."

    I originally selected 5 tables all from dbo and all wildcarded that had references to each other. The idea is I could then in PowerBI do a report on user activity. 

    Just pointing this out in case there is some significance to this statement.

    Also, I suspect there is some version incompatibility or setting on the source database or the error is masking an unrelated error as I would think a GUID should map to a GUID without issue (I also tried select rowguid from [dbo].Users and had the same result). Have you heard of anything?


    Cheers, Jeff

    Thursday, April 12, 2018 9:19 PM
  • Please take a look at the following: Resolving Transact-SQL differences during migration to SQL Database

    I suspect you are attempting to copy over some aspects of dbo.Users that is not supported, such as permissions (discussed in document link).

    Regards,

    Mike

    Thursday, April 12, 2018 10:29 PM
  • I was able to resolve this migration using the Data Warehouse Migration Utility to generate bat scripts and then using BCP.

    What I found was the migration script generated was change the uniqueidentifier to varchar(38) and then to uniqueidentifier. Using the DWM utility, I updated the migration script to not perform a cast.

    Because of this I believe Azure Data Factory is generating the script in the same way as DWM and because you cannot edit the migration script, the script fails due to the conversion of varchar(38) to uniqueidentifier.

    Thanks again Mike.


    Cheers, Jeff

    Friday, April 13, 2018 12:00 AM