I have a stored procedure to return SHA2_256.

CREATE FUNCTION [dbo].[Checksum_SHA_256]
    (@Input NVARCHAR(100))
RETURNS BINARY(32)
AS
BEGIN
    RETURN HASHBYTES('SHA2_256', @Input)
END

I have an UPDATE statement :

UPDATE t1
SET Checksum_ = [dbo].[Checksum_SHA_256](CONCAT(
                            ISNULL(CAST(col1 AS VARCHAR (100)), N'NULL'), N'|',
                            ISNULL(CAST(col2 AS VARCHAR (100)), N'NULL'), N'|',
                            ISNULL(CAST(col3 AS VARCHAR (100)), N'NULL'), N'|',
                            ISNULL(CAST(col4 AS VARCHAR (100)), N'NULL'), N'|',
                            ISNULL(CAST(col5 AS VARCHAR (100)), N'NULL'), N'|',
                            ISNULL(CAST(col6 AS VARCHAR (100)), N'NULL'), N'|'))
FROM TABLE t1

That query works fine in SSMS.

But when I add it as an “Execute SQL Task” in a SSIS package, the UPDATE statement does not work correctly.

All of the values for the column return :

0x30965CF7550200000AD00000B8FF6B480000000000000000000000000D000004

The Checksum column is defined as a binary(32) column.

Any suggestions on how to fix this?