In ssis ,Is there anyway to encrypt the data with a password in the pipeline 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

Why would you want to encrypt the data stream hitting a file within your computing domain? SFTP is a secure method of communications enough to protect your data. Depends on your scenario you may want to implement MSMQ or a WebService, there are other technologies, too. But it depends what is the target system. Arthur My Blog

In ssis ,Is there anyway to encrypt the data with a password There are some third party encryption components available one http://ssisctc.codeplex.com/ But you could also do it with a Script Task and some .net code in the pipeline just before writing to the csv which i have to upload it to sftp Please explain. Uploading a file before writing the file? 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 I think you need some .net code here aswell (if its possible). Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Any help on how to encrypt the data just before loading the data into flat file

Think about how it will be decrypted on the other end (e.g. by the third party). Do you know how? I think you have some misconception about the encryption, I guess what you need is merely protect the data in transit.Arthur My Blog

i need to protect the data in transit. We are using PGP encryption. at the other end they will pgp to decrypt the csv file

Here you go, now it is more informative, you need to use an Execute Process Task to encrypt the file using the command line once the file is already produced and on say a local drive. See a related post: http://social.msdn.microsoft.com/Forums/eu/sqlintegrationservices/thread/f5c4faf5-a18b-4355-9740-132e41256d9aArthur My Blog

The link you have provided is for encryption after the file is created. I need to add encryption first and then create the file.

Why?Arthur My Blog

We are giving the csv files to thirdparties .only some of the thirdparty members are allowed to view the csv files. So the party party wants the encrypted file out of the package unlike the way to add the encryption once after the file is created

Sachin, Lets take a step back, can we just understand in details the requirement: 1. You need to encrypt data using some key, as this is secure data. 2. THis needs to be done while the data in transit, htats why you are trying to use SFTP. 3. The data is is decrypted once the file are SFTPed, and converted back to as required. Have you considered how the data which you have encrypted using your key would be recognized on to the client side once you have SFTped them? I would have thought that this is what you require: a) Create a .csv file, with the data decrypted, store this is in a safe location. b) SFTP the files using third party free tools like filezilla, this would meet the requirement of transitioning the file in a secure mode. Abhinav

Arthur's question stands - why do you need the contents of the file encrypted? You can generate the CSV file in a protected folder - you can easily restrict access to that folder based on account. You can then encrypt the file in that protected area before sending it over SFTP. Talk to me now on