locked
ADFV2 fails when loading data over 8000 CHARACTERS to Data Warehouse RRS feed

  • Question

  • Hi Community,

    I am having an issue on Data Warehouse regarding the limitation of field characters, some of the fields from Enablon OData (SOURCE) have exceeded the maximum limit of [VARCHAR](8000), currently all the fields within Data Warehouse are using the same datatype [VARCHAR](8000). According to Microsoft documentation (https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017), We can use [VARCHAR](max) for fields larger than 8000 characters but fails when running the script i am not sure if this is only applicable to a SQL instance not Data Warehouse.

    Regards 
    Mbulelo M

    • Edited by Mbulelo M Thursday, December 6, 2018 11:19 AM
    Thursday, December 6, 2018 11:15 AM

All replies

  • Hi,

    The Varchar(8000) is the max wide column when you use Polybase to load data into SQL DW. This is a limitation in SQL DW side.

    Now you can either not use Polybase to load the data, or you can leverage the skip faulty rows in Polybase to reject such rows.

    Friday, December 7, 2018 4:07 AM
  • Hi Yingqin,

    I tried not using Polybase but the results are the same, the pipeline failed. As for providing the reject value is not ideal as my requirements are to land all the data in Data Warehouse for real-time reporting therefore no row must be left behind or rejected.

    Please let me know if this SQL DW limitation can be added on your next road map or if there's a workaround that works without skipping or rejecting faulty columns?

    Regards,

    Mbulelo

    Monday, December 10, 2018 5:35 AM
  • Hi,

    Please make sure your target table's column type is varchar(Max). If no findings, you can paste your activity run ID here, and we can help take a quick look.

    Tuesday, December 11, 2018 5:28 AM