Post reply

Best way to add last 4 of SSN to data feed in SSIS from encrypted SSN column

  • March 31, 2022 at 9:10 pm

    #4009005

    We have a table with encrypted SSN and I need to add the last 4 in an ETL

    I can open the master key and read the data but don't want a pw in plain text in the ETL package and I don't want to encrypt the ETL, but I could if that's the only option. But I'm working on a branch, not even sure if git could merge that if it was encrypted.

    I'd rather have it in a config so I can simply change the pw for Test/Prod without needing to modify the package.

    Maybe I'm thinking about this all wrong. Any suggestions?

  • April 1, 2022 at 2:34 pm

    #4009358

    Nevermind - It's too bad there's not a delete option for posts.

  • April 1, 2022 at 3:11 pm

    #4009382

    TangoVictor wrote:

    Nevermind - It's too bad there's not a delete option for posts.

    What did you end up doing to solve your problem?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 6, 2022 at 9:51 pm

    #4011854

    I was thinking I needed to include a password however that's not the case and why I wanted to delete this after I realized that.

    Anyway I including the column in the SSIS OLE Source query, it's all I needed. The proxy account we use has cert access so it's able to decrypt this data.

    RIGHT(CONVERT(VARCHAR(9),DECRYPTBYKEYAUTOCERT(CERT_ID('ourCert'), NULL, ssnEncrypted)),4)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply