processing encrypted files with SSIS (flat file source)

Question

Any recommendations on how to process (file-level) encrypted data with SSIS? We'd like to avoid any un-encrypted content on disk, and instead read the encrypted data into memory (or the database,) decrypt using .NET, then proceed with transform & load.

The challenge is, the flat file source only works with flat files. The same challenge applies to generating data, that is with encrypting sensitive data before it is written to disk. The standard flat file destination only supports writing plain text data to disk. Are there any components that allow for SSIS to consume plain text data that's stored in memory, or in the database (nvarchar(max)) or similar?

The COTS solutions we're aware that deal with encrypted files - from /n software and CozyRock - only operate on files. So that wouldn't help.

One possible solution seems to be to write a custom implementation of the flat file source, see link below for an example (for regex parsing as opposed to decrypting.) However that approach seems cumbersome. I'd also be concerned about incompatibilities with future SQL/SSIS versions.

Any recommendations much appreciated!

(Link to custom flat file source implementation)
https://github.com/fan130/mssql-ssis-community-samples/releases/tag/RegularExpressionFlatFileSource

Answer 1

Hi Roman,

Please try to use Script Task to encrypt the files.

Please refer to Encrypt files in SSIS.

Best Regards,
Mona

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi Roman,

COZYROC provides unique in-memory streaming of data technology that allows you to process data without landing the data into a file first. I would recommend you check the samples we have posted on this documentation page. Do not hesitate to contact our support desk if you have further questions.