Output a HASHBYTES SHA2_256 Encrypted Data Column to a Flat File Destination

Bobby P 231 Reputation points
2021-01-21T12:07:09.257+00:00

I need to Encrypt a data column so we are using the HASHBYTES 'SHA_256' Method via a SQL Server Stored Procedure. When we output this data column to a Flat File Destination, it is resulting in unreadable characters.

â_ Ç ›è%òIÙ 8!Í v ·z +Ie 7ª 9 ²

Is there something I need to do in the Advanced Editor of the OLE DB Source in order to get this to flow to the Flat File Destination, a simple pipe-delimited .txt file, so the encrypted ID in this case is readable. When we run via SSMS the output looks just fine. It's when it tries to Data Flow to the Flat File Destination via the SSIS Package that it's getting messed up and coming out looking really ugly.

çC "åˆÔ»ÈÈùB$ô Ó¿K[ˆžº ¥Ibä VQ

If there is a better way to encrypt the data coming from the SQL Server Stored Procedure rather than using HASHBYTES 'SHA_256', then I'm all in.

Thanks for your review and am hopeful for a reply.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,590 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bobby P 231 Reputation points
    2021-01-23T22:51:14.083+00:00

    I actually had to CONVERT the HASHBYTES Result to a VARCHAR in order for it to appear within the Flat File via the SSIS Package

    			CONVERT (VARCHAR(MAX), HASHBYTES ('SHA2_256', CONVERT (VARCHAR, [#TempTable_Enrollments].[EnrollmentID])), 1) AS [Enrollment ID - Encrypted],
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. COZYROC 101 Reputation points
    2021-01-22T01:32:16.46+00:00

    After you do the data encryption, you have to do base64 encoding of the data to make it store properly in a text file.

    0 comments No comments

  2. Monalv-MSFT 5,901 Reputation points
    2021-01-22T08:56:55.317+00:00

    Hi @Bobby P ,

    1.When we run via SSMS the output looks just fine.
    Could you please share the output in SSMS?
    59542-outputinssms.png

    2.Please preview the result of sql query in OLEDB Source Editor.
    May I know if you want to load encrypted data to Flat File?
    Please refer to the following pictures:
    59543-oledbsource-previewdata.png
    59435-output.png

    3.If there is a better way to encrypt the data coming from the SQL Server
    Please refer to Configure column encryption using Always Encrypted Wizard.

    4.We can set Protection Level to encrypt sensitive data in ssis package.
    Please refer to Access Control for Sensitive Data in Packages.

    Best Regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.