0

I have a sql DB which stores hash value in the column of binary data type. I use sql hashbytes of shah256 algorithm in my sql stored procedure to find hash value and store in the db. I want to replicate the same in Azure data factory. I'm seeing sha2(256) algorithm in derived expression in data flow and i'm trying to utilise this function. But this returns the string and i'm trying to convert this to binary and inserting into sql database using sink activity. But i'm getting the below error.

{"message":"at Sink 'EmployeeInsert': java.sql.BatchUpdateException: String or binary data would be truncated.. Details:at Sink 'EmployeeInsert': java.sql.BatchUpdateException: String or binary data would be truncated.","failureType":"UserError","target":"EmployeeDataFlow","errorCode":"DFExecutorUserError"}

Hope i have explained my problem in detail and any help would be appreciated. Thanks in advance.

CHEEKATLAPRADEEP
  • 12,191
  • 1
  • 19
  • 42
javababy
  • 17
  • 10
  • where is the conversion from string to binary happening? It looks like you are doing that casting in Stored Procedure. Correct? – Prawin Aug 11 '20 at 14:04
  • And, Can you tell me what is the length of the Stored Procedure parameter which accepts your hash value? – Prawin Aug 11 '20 at 14:05
  • i'm converting in derived column of data flow activity. I'm not using any stored procedure here. This is the sample expression toBinary(sha2(256,string)) – javababy Aug 11 '20 at 14:06
  • The error is related to the length of a column. What is the datatype of the column in the table? is it binary or something similar? Are you sure it's not something similar to varchar – Prawin Aug 11 '20 at 14:42
  • Its binary(32). – javababy Aug 11 '20 at 14:48
  • Can you try increasing the size and run the pipeline. it should resolve the issue – Prawin Aug 11 '20 at 14:55
  • the db column type shouldn't be changed. Any other idea please? because this is not the case when i calculate the hash value in sql side. – javababy Aug 11 '20 at 15:13
  • 1
    I don't think sha2(256) will fit in binary(32). Try binary(64). You can always use length(sha2(256,columns)) in your data flow expression to confirm the length of the string to see if it will fit into that conversion. – Mark Kromer MSFT Aug 11 '20 at 19:44
  • Definitely the problem is with the length. Please try the length function as @MarkKromer suggested – Prawin Aug 12 '20 at 02:11
  • Hi @javababy, is the error solved now? – Leon Yue Aug 18 '20 at 01:17
  • @LeonYue Nope. We just looked for alternatives. Thanks. – javababy Nov 26 '20 at 17:08

0 Answers0