0

I am getting an error while performing the copy of a given on prem table to ADW, I have checked the data types and all, all looks good here but I dont know what breaking or whats been going on here, so that I can figure it out, Please have a look at the below error, also I am not using the polybase here as In on prem sql server there are text datatypes so for those I am using the varchar(max) approach, as polybase does not work with max data types.

Error:

Copy activity encountered a user error at Sink:adatawarehouse.database.windows.net side: 'Type=System.OverflowException,Message=Array dimensions exceeded supported range.,Source=Microsoft.DataTransfer.Common,'.

John Wat
  • 11
  • 3

2 Answers2

0

Take a look at the warehouse's limitations here: https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-service-capacity-limits

Also read this: https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-data-types

It looks like Azure Data warehouse doesnt like varchar(max) very much, and its reasonable because its built to support a business intelligence architecture, where long text fields aren't useful to show the big picture of a company in summarized data. Try using varchar(8000) instead!

Hope this helped!

Martin Esteban Zurita
  • 3,161
  • 12
  • 23
  • I have tried using the varchar(8000) but got the same results instead – John Wat Feb 06 '18 at 19:09
  • Ok then, try casting those fields in the sql query in the activity. CAST([fieldName] as varchar(8000)) as [fieldName] – Martin Esteban Zurita Feb 06 '18 at 19:17
  • What this will do? – John Wat Feb 06 '18 at 19:28
  • It will query the field as if its data type is a varchar(8000) and not a text. When it goes over 8000 characters, it just truncates the data to fit in 8000. – Martin Esteban Zurita Feb 06 '18 at 19:59
  • But I need all of the data that is present inside the On prem server, I cant just truncate non compatible data! Thats why I was using varchar(max) with HEAP in ADW to get the job done, but I was failing. – John Wat Feb 06 '18 at 20:01
  • Then dont use a data warehouse! As I said in my first answer, the data warehouse is meant to support a business intelligence architecture, where long texts dont add any value because they cant be summarized. What you need is a database in SQL server if you want to store long texts. – Martin Esteban Zurita Feb 07 '18 at 19:25
  • Ok Thanks for your response. – John Wat Feb 08 '18 at 08:15
0

I was able to solve this by not selecting the Blob Staging, as I was not using the polybase technique that involves blobs for staging, There was something wrong with the blob in the storage account the values are not getting correctly inserted into the blob so they are not getting inserted into the ADW.

But I dont understand if we dont want to use polybase then we also have an option to use Staging Storage account why is that there??

Apparently the issue was resolved.

John Wat
  • 11
  • 3