locked
Data Flow - sha2 function in Binary format RRS feed

  • Question

  • Hi,

    is it possible to convert the string generated via sha2 (256 in our case) function on ADF Data Flow so that it could be saved in a Binary(32) typed column in SQL Sever Database? Currently we have to save it as CHAR(64). We are hoping to improve the overall query performance by reducing the size of the column with Binary(32).

    Any help is greatly appreciated!

    Cheers,
    HY Shum

    Thursday, April 9, 2020 9:27 AM

All replies

  • Did you try casting in the expression with toBinary()?
    Thursday, April 9, 2020 7:36 PM
  • Hello HY Shum and thank you for your question.

    Could you please try the expression:

    toBase64(sha2(256, 'my_stuff'))

    Here is a related MSDN thread.


    Friday, April 10, 2020 12:14 AM
  • Hi Mark,

    I did but when writting the data into the Binary(32) column in SQL Server Database, I encountered the error:

    String or binary data would be truncated in table '<TableName>', column '<ColumnName>'

    It seems that the converted value is longer than Binary(32)..

    Tuesday, April 14, 2020 7:59 AM
  • I tried toBase64 too but when writting the data into the Binary(32) column in SQL Server Database, I encountered the error:

    String or binary data would be truncated in table '<TableName>', column '<ColumnName>'

    It seems that the converted value is longer than Binary(32)

    p/s: the URL in your reply is pointing back to this thread. Could you resend me the correct link? Thanks

    Tuesday, April 14, 2020 8:01 AM
  • Does anyone have further clue?
    Friday, April 17, 2020 6:09 AM
  • Friday, April 17, 2020 8:04 PM
  • If you provide me with the dataflow plan and sink dataset, I can try to reproduce and take a look.

    If you do not feel comfortable posting the code here, you can email to:

    AzCommunity@microsoft.com

    Please include the URL of this page and my name so I can locate the email more easily.

    Friday, April 17, 2020 8:09 PM
  • Hi Martin,

    Thanks for the reply. I think the other MSDN thread does not answer my question. I have sent my sample data flow to the email address provided by you. Looking forward to hearing from you.

    Cheers


    • Edited by HYShum Wednesday, April 22, 2020 6:50 AM
    Wednesday, April 22, 2020 6:49 AM