none
Issue while copying data from blob storage to ADWH. if i change ReasonID column is NOT NULL to NULL then it is working fine.but it is throwing the error for other columns i think issue with the back slash and forward slash.Could you please let me know? RRS feed

  • Question

  • error:

    { "errorCode": "2200", "message": "'Type=System.InvalidOperationException,Message=Column 'ReasonID' does not allow DBNull.Value.,Source=System.Data,'", "failureType": "UserError", "target": "Copy data2" }

    


    • Edited by pankaj92 Monday, October 21, 2019 2:12 PM
    Monday, October 21, 2019 2:09 PM

Answers

  • problem with Carriage return \r. so even i tried with line feed, auto detect while copying from Blob to ADWH.and also tried  with REPLACE(column_name,char(10),'  ').But still unable to fix the issue.Please see below attached screenshot, hope it will give you more clarification.I am looking forward your response.


    Download the generated file from Blob to local and open in Notepad++, enable View --> Show symbols --> all characters. 

    See what's wrong with the file. The data row shouldn't have any enter in between and there should be only new line char at the end. 

    Also check if you have CHAR(13) in your data along with CHAR(10). Both these chars should be replaced with some dummy value. 

    For reference:


    Regards,
    Vaibhav

    • Edited by Vaibhav-Chaudhari Thursday, October 24, 2019 10:08 AM
    • Marked as answer by pankaj92 Saturday, October 26, 2019 12:37 AM
    Thursday, October 24, 2019 10:07 AM

All replies

  • Hello pankaj92 and thank you for your question.  If I understand correctly, the error is asking for each column to be null-able.  Could you share a couple rows of data and show me why you think forward-slash or back-slash is the issue?
    Monday, October 21, 2019 5:12 PM
    Moderator
  • Hi Martin,

    Thanks for your quick response.!

    In my current scenario i am copying  tables from Azure SQL DB to Blob Storage and Blob storage to Azure SQL DWH.So in first copy activity there is no error it is running without any error and data is storing into Blob storage.As you know  in Blob  storage everything is stores in String Format , once i run my second copy activity for  copying data from blob storage to Azure SQL DWH it is throwing error.I am not sure about  forward-slash or back-slash is the issue.But currently i am unable to rectify what is the issue which is failing my copy activity even i tried to change everything like Row Delimiter,Escape character etc.

    EX:

    Create table Name Product(Id int  Not Null,NAME VARCHAR(100) NOT NULL, Product Description VARCHAR(200) Not Null)

    Once i tried to execute SQL query: 

    SELECT * FROM Product WHERE Product Description  LIKE '%' + CHAR(10) + '%'  

    it is showing some data in my Product Description column due to that activity is failing.

    Hope now it is clear .Thanks.!.

    Kindly share your views now.

    Best Regards,

    Pankaj

    Monday, October 21, 2019 6:01 PM
  • If you are using the GUI, I recommend you try the "Preview data" button found in the "Source" tab of the copy activity.  This should give you a rough idea of whether the file is being read correctly or not.  "Preview data" will fail if the file is parameterized, so I suggest temporarily hard-coding the filepath for testing.

    Monday, October 21, 2019 8:58 PM
    Moderator
  • Hello Pankaj, are you still having problems, or did my suggestion help?
    Wednesday, October 23, 2019 12:13 AM
    Moderator
  • Hi Martin,

    Thanks for your response.!

    I found the problem with the columns data ,  that is newline character CHAR(10) problem in columns so i am replacing with empty space but still persisting the same.Do you have any idea kindly let me know.?

    Thanks,

    Pankaj

    Wednesday, October 23, 2019 1:41 AM
  • If I understand correctly, your are saying that your data in (csv form) looked like:

    data1\n,data2\n,data3\n\n

    and you changed data in the source to be like:

    data1 ,data2 ,data3 \n

    I noticed that in the screenshot, your newline character was set to \r.  This means the data should look like:

    data1 ,data2 ,data3 \r

    Is this correct?

    Wednesday, October 23, 2019 6:03 PM
    Moderator
  • Hi Martin,

    Thanks for your response.

    Yes, you are Correct.

    executed below query:-

    SELECT FROM Product WHERE Product Description  LIKE '%' + CHAR(10) + '%' 

     problem with Carriage return \r. so even i tried with line feed, auto detect while copying from Blob to ADWH.and also tried  with REPLACE(column_name,char(10),'  ').But still unable to fix the issue.Please see below attached screenshot, hope it will give you more clarification.I am looking forward your response.

    <IMAGE DELETED BY MODERATOR>

    It is working fine for 1st copy activity Azure SQL DB to Blob Storage,Failing for 2nd copy activity from Blob Storage to ADWH.

    Thanks,

    Pankaj




    Thursday, October 24, 2019 9:27 AM
  • problem with Carriage return \r. so even i tried with line feed, auto detect while copying from Blob to ADWH.and also tried  with REPLACE(column_name,char(10),'  ').But still unable to fix the issue.Please see below attached screenshot, hope it will give you more clarification.I am looking forward your response.


    Download the generated file from Blob to local and open in Notepad++, enable View --> Show symbols --> all characters. 

    See what's wrong with the file. The data row shouldn't have any enter in between and there should be only new line char at the end. 

    Also check if you have CHAR(13) in your data along with CHAR(10). Both these chars should be replaced with some dummy value. 

    For reference:


    Regards,
    Vaibhav

    • Edited by Vaibhav-Chaudhari Thursday, October 24, 2019 10:08 AM
    • Marked as answer by pankaj92 Saturday, October 26, 2019 12:37 AM
    Thursday, October 24, 2019 10:07 AM
  • Hi Vaibhav,

    Thanks for your response.

    I followed the same as you advised me and my data is coming in the same format as you mentioned in above screenshot but i am unable to rectify the issue. I tried all the possible ways (like replacing carriage return,column- delimiter etc.)but still my pipeline is failing for copy activity because of data issue.Even i tried to load file in my on-premises DB also, it is not importing successfully.Kindly let me know why it is happening.?

    Thanks,

    Pankaj

    Friday, October 25, 2019 6:17 PM
  • Hello pankaj92, I noticed that the table you provided had email addresses in it.  This is a public forum, and I was unsure whether you intended to make this information visible to others.  To be safe, I deleted your post.  Could you please email AzCommunity@microsoft.com  with "404e21ae-7140-49da-b517-a3cf3b9aee1d" (this thread's id) in the subject?
    Wednesday, October 30, 2019 12:17 AM
    Moderator
  • I also deleted the image in your other post, as this contained sensitive information as well.
    Wednesday, October 30, 2019 12:29 AM
    Moderator
  • Hi Martin,

    Thanks for informing me. I will look into it.

    Regards,

    Pankaj

    Wednesday, October 30, 2019 5:37 AM