Post reply

encrypt the data in the pip

  • 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

  • July 7, 2011 at 12:55 pm

    #1349951

    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

  • July 7, 2011 at 1:52 pm

    #1350004

    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

  • July 7, 2011 at 3:42 pm

    #1350071

    I am planning to use pgp encryption.

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

  • July 8, 2011 at 9:23 am

    #1350418

    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

  • July 14, 2011 at 8:08 am

    #1352842

    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/

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

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