encrypt the data in the pip

Question

encrypt the data in the pip

Sachin4all

Ten Centuries

Points: 1249
More actions
July 7, 2011 at 12:53 pm

#389928

In ssis ,Is there anyway to encrypt the data with a password just before writing to the csv which i have to upload it to sftp.
and also is it possible to store the data in memory to upload it to sftp instead of writing it to csv file
as it is sensitive data i am trying to elminating the csv file and and also adding security to the data

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You would need to write the data out to file. If you encrypt the data in the file, you would need to pass your key to decrypt the data to whomever you upload this data to via sftp.

You could zip and password protect the zip file to pass along via sftp.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I largely agree, the destination components do not have this functionality. You might be able to build a Script Task as a destination to handle it but that is going to be somewhat painful. Writing the file then encrypting it is probably the best. Also keep in mind that old style ZIP encryption should be avoided, it was not very secure. you will want to be sure to use something like AES-256. and if you zip it it will likely reduce the file size..

Also, if you are sending an encrypted file, what is the rationale behind using SFTP, the data is already encrypted sending encrypted data over an encrypted pipe seems unnecessary..

CEWII

Sachin4all Ten Centuries Points: 1249 More actions · Answer 3

I am planning to use pgp encryption.

at the decryption side they will use the pgp software to decrypt the file

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

just use a script task to encrypt the csv file that you create.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

Sachin4all (7/7/2011)
In ssis ,Is there anyway to encrypt the data with a password just before writing to the csv which i have to upload it to sftp.
and also is it possible to store the data in memory to upload it to sftp instead of writing it to csv file
as it is sensitive data i am trying to elminating the csv file and and also adding security to the data

If you can use third-party solutions, check the commercial CozyRoc SSIS+ library. The components are designed to support the so-called "in-memory streaming" without creation of intermediate files. You can:

Write data to in-memory stream -> Encrypt the stream with OpenPGP -> Upload the stream to SFTP site

all without any files created in-between. We have customers who need this kind of security where intermediate file creation is not desired.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/