none
SQL Server Migration Assistant for Oracle - Problems converting UUID RAW(16) to uniqueidentifier RRS feed

  • Question

  • Hi

    I am trying to migrate a UUID field from Oracle type RAW(16) to SQL Server 2016 type uniqueidentifier.

    Creating the table works so far. I changed the "Colums type mapping" from raw to uniqueidentifier (was set to varbinary).

    https://imgur.com/pH5DxaP

    The table is therefore created correctly with uniqueidentifier.

    I have problems with the data migration. I get the following error:

    The specified value of type Byte[] from the data source cannot be converted into type uniqueidentifier of the specified target column.

    https://imgur.com/Y6fsPUU

    I tried to interpose an individual select via the "Extended data migration options".

    https://imgur.com/42gsK5P

    The column "UUID" has been replaced by:

    LOWER(SUBSTR("UUID", 1, 8) || '-' || '-' || SUBSTR("UUID", 9, 4) || '-' || SUBSTR("UUID", 13, 4) || '-' || SUBSTR("UUID", 17, 4) || '-' || SUBSTR("UUID", 21, 12)) "UUID"

    https://imgur.com/fEkipRm

    This one makes me a string that looks like this:

    8e086d49-9bdb-4ae4-e053-638da8c044b5

    But that won't work either. The same error appears, but instead of "Byte[]" with "String"

    The data migration works if I replace "UUID" with NULL. However, this is of no use to me.

    Is there a solution how to get the UUID's correctly into the uniqueidentifiers?
    Monday, October 14, 2019 11:51 AM

All replies

  • Hi Clemaram,

     

    ORACLE SYS_GUID is RAW(16) and it is 32 character hexadecimal representation.SQL Server datatype Uniqueidentifier, is 16 byte binary value, which is 36 character representation. SQL Server UniqueIdentifier

     

    You can think of below options:

    I would suggest you to use VARCHAR(32) to represent corresponding ORACLE GUID in SQLServer in the typemapping.

     

    you can have a default value for NEWID() for the target column, values will get assigned when you load the data

     

    Have the target datatype as VARCHAR(36) and once you are done with migration, you can start using NEWID() for future values. As GUIDs are going to be unique, you will not face

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 15, 2019 6:57 AM
  • I would suggest you to use VARCHAR(32)
    Migration to varbinary works fine. But I need to migrate into uniqueidentifier.

    Of course I could convert the varbinary after the migration to a uniqueidentifier. But this is very time-consuming in my case, because indices, default values and constraints hang on the UUID fields. The Database has around 500 TAbles.
    Tuesday, October 15, 2019 7:50 AM
  • Hi Clemaram,

     

    This seems to be the only way for you now.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 15, 2019 8:09 AM
  • Hi,

    SSMA is not really an ETL tool, so the amount of transformations is very limited. You can convert schema using SSMA and use SSIS to migrate the data, where you will have more options to transform it along the way. In SSMA, I think migrating as binary and converting the column to uniqueidentifier post-migration is the best way to go at this moment.

    We will put an item in our backlog to improve conversion from different source data types to uniqueidentifier in the target during data migration, but it's not really a high-priority for us, as we try to focus more on schema conversion aspects of the tool.

    Thanks,
    Alex.

    Tuesday, October 22, 2019 6:29 AM