Encrypt files in SSIS

Case
Last month I did a post about encrypting certain columns with a Script Component. But I got questions about encrypting the whole file. So here are a couple of solutions.

Solutions
A) You could use an Execute Process Task to call an external executable which does the encryption for you. I personally don't like this task (feels you're a bit out of control), but it works. See this blog example and there are plenty of examples in the SSIS forum.
B) There are a couple of third party tasks that will do the encryption for you: BizCrypto, CozyRoc OpenPGP and PGP Encryption Task.
C) You could use the Script Task with some .Net coding to do it yourself.

Let's elaborate the Script Task solution:

1) Baseline Situation
The baseline situation of this case is an export from a database table to a CSV file. We will encrypt the CSV file when the data flow task is finished.

Baseline Situation

2) Variable
Create a string variable named EncryptionKey to store your encryption key in and give it a value.

Rightclick in the Control Flow

3) Script Task
Add a Script Task to the Control Flow. Give it a suitable name and connect it to the Data Flow Task.

Script Task

4) Select Variable
Edit the Script Task and select the variable EncryptionKey from step 2 as a ReadOnlyVariable.

Readonly variable

5) The Script
Hit the Edit Script button and copy the code below. The script contains an extra row to show you how you can decrypt the file again. I have created two new static methods (Encrypt and Decrypt) that will do the job. They are simplified versions of these examples at CodeProject. If you want to use these scripts commercially, make sure you read the documentation and test it thoroughly! I don't take any responsibility whatsoever.

// C# code
using System;
using System.Data;
using System.IO;                        // Added 
using System.Security.Cryptography;     // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_2b17b0d6160d4866b944d20ce45d0994.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Get the filepath of the file that needs to be encrypted. This is
            // the name of the connection manager.
            string filepath = Dts.Connections["UsersExport"].ConnectionString;

            // Determine new name: d:\folder\file.csv => d:\folder\file_ENCRYPTED.csv
            string newFilepath = filepath.Substring(0, filepath.LastIndexOf(".") -1) + "_ENCRYPTED" + filepath.Substring(filepath.LastIndexOf("."));

            // Get password from SSIS variable
            string encryptionKey = Dts.Variables["EncryptionKey"].ToString();
            
            // Create an encrypted copy of the file
            Encrypt(filepath, newFilepath, encryptionKey);

            // ADDED JUST FOR TESTING: Create a decrypted copy of the encrypted file
            Decrypt(newFilepath, newFilepath.Replace("_ENCRYPTED","_DECRYPTED"), encryptionKey);

            // Close Script Task
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        // Encrypt textfile with Rijndael encryption 
        public static void Encrypt(string fileIn, string fileOut, string Password)
        {
            // Open filestream for source file
            FileStream fsIn = new FileStream(fileIn, FileMode.Open, FileAccess.Read);

            // Open filestream for encrypted file
            FileStream fsOut = new FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write);

            // Create Key and IV from the password with salt technique 
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});

            // Create a symmetric algorithm with Rijndael 
            Rijndael alg = Rijndael.Create();

            // Set Key and IV 
            alg.Key = pdb.GetBytes(32);
            alg.IV = pdb.GetBytes(16);

            // Create a CryptoStream 
            CryptoStream cs = new CryptoStream(fsOut, alg.CreateEncryptor(), CryptoStreamMode.Write);

            // Initialize a buffer and process the input file in chunks. 
            // This is done to avoid reading the whole file (which can be huge) into memory. 
            int bufferLen = 4096;
            byte[] buffer = new byte[bufferLen];
            int bytesRead;

            do
            {
                // read a chunk of data from the input file 
                bytesRead = fsIn.Read(buffer, 0, bufferLen);

                // encrypt it 
                cs.Write(buffer, 0, bytesRead);
            } while (bytesRead != 0);

            // close everything 
            // this will also close the unrelying fsOut stream
            cs.Close();
            fsIn.Close();
        }

        // Decrypt textfile with Rijndael encryption 
        public static void Decrypt(string fileIn, string fileOut, string Password)
        {
            // Open filestream for encrypted source file
            FileStream fsIn = new FileStream(fileIn, FileMode.Open, FileAccess.Read);

            // Open filestream for decrypted file
            FileStream fsOut = new FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write);

            // Create Key and IV from the password with salt technique 
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});

            // Create a symmetric algorithm with Rijndael 
            Rijndael alg = Rijndael.Create();

            // Set Key and IV 
            alg.Key = pdb.GetBytes(32);
            alg.IV = pdb.GetBytes(16);

            // Create a CryptoStream 
            CryptoStream cs = new CryptoStream(fsOut, alg.CreateDecryptor(), CryptoStreamMode.Write);

            // Initialize a buffer and process the input file in chunks. 
            // This is done to avoid reading the whole file (which can be huge) into memory.
            int bufferLen = 4096;
            byte[] buffer = new byte[bufferLen];
            int bytesRead;

            do
            {
                // read a chunk of data from the input file 
                bytesRead = fsIn.Read(buffer, 0, bufferLen);

                // Decrypt it 
                cs.Write(buffer, 0, bytesRead);
            } while (bytesRead != 0);

            // close everything 
            // this will also close the unrelying fsOut stream 
            cs.Close(); 
            fsIn.Close();
        }
    }
}

Or VB.Net

' VB.Net code
Imports System
Imports System.Data
Imports System.IO                       ' Added
Imports System.Security.Cryptography    ' Added 
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 

 Public Sub Main()
        ' Get the filepath of the file that needs to be encrypted. This is
        ' the name of the connection manager.
        Dim filepath As String = Dts.Connections("UsersExport").ConnectionString

        ' Determine new name: d:\folder\file.csv => d:\folder\file_ENCRYPTED.csv
        Dim newFilepath As String = filepath.Substring(0, filepath.LastIndexOf(".") - 1) & "_ENCRYPTED" & filepath.Substring(filepath.LastIndexOf("."))

        ' Get password from SSIS variable
        Dim encryptionKey As String = Dts.Variables("EncryptionKey").ToString()

        ' Create an encrypted copy of the file
        Encrypt(filepath, newFilepath, encryptionKey)

        ' ADDED JUST FOR TESTING: Create a decrypted copy of the encrypted file
        Decrypt(newFilepath, newFilepath.Replace("_ENCRYPTED", "_DECRYPTED"), encryptionKey)

        ' Close Script Task
        Dts.TaskResult = ScriptResults.Success
 End Sub

    ' Encrypt textfile with Rijndael encryption 
    Public Shared Sub Encrypt(ByVal fileIn As String, ByVal fileOut As String, ByVal Password As String)
        ' Open filestream for source file
        Dim fsIn As New FileStream(fileIn, FileMode.Open, FileAccess.Read)

        ' Open filestream for encrypted file
        Dim fsOut As New FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write)

        ' Create Key and IV from the password with salt technique 
        Dim pdb As New PasswordDeriveBytes(Password, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, &H65, &H64, &H76, &H65, &H64, &H65, &H76})

        ' Create a symmetric algorithm with Rijndael 
        Dim alg As Rijndael = Rijndael.Create()

        ' Set Key and IV 
        alg.Key = pdb.GetBytes(32)
        alg.IV = pdb.GetBytes(16)

        ' Create a CryptoStream 
        Dim cs As New CryptoStream(fsOut, alg.CreateEncryptor(), CryptoStreamMode.Write)

        ' Initialize a buffer and process the input file in chunks. 
        ' This is done to avoid reading the whole file (which can be huge) into memory. 
        Dim bufferLen As Integer = 4096
        Dim buffer As Byte() = New Byte(bufferLen - 1) {}
        Dim bytesRead As Integer

        Do
            ' read a chunk of data from the input file 
            bytesRead = fsIn.Read(buffer, 0, bufferLen)

            ' encrypt it 
            cs.Write(buffer, 0, bytesRead)
        Loop While bytesRead <> 0

        ' close everything 
        ' this will also close the unrelying fsOut stream
        cs.Close()
        fsIn.Close()
    End Sub

    ' Decrypt textfile with Rijndael encryption 
    Public Shared Sub Decrypt(ByVal fileIn As String, ByVal fileOut As String, ByVal Password As String)
        ' Open filestream for encrypted source file
        Dim fsIn As New FileStream(fileIn, FileMode.Open, FileAccess.Read)

        ' Open filestream for decrypted file
        Dim fsOut As New FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write)

        ' Create Key and IV from the password with salt technique 
        Dim pdb As New PasswordDeriveBytes(Password, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, &H65, &H64, &H76, &H65, &H64, &H65, &H76})

        ' Create a symmetric algorithm with Rijndael 
        Dim alg As Rijndael = Rijndael.Create()

        ' Set Key and IV 
        alg.Key = pdb.GetBytes(32)
        alg.IV = pdb.GetBytes(16)

        ' Create a CryptoStream 
        Dim cs As New CryptoStream(fsOut, alg.CreateDecryptor(), CryptoStreamMode.Write)

        ' Initialize a buffer and process the input file in chunks. 
        ' This is done to avoid reading the whole file (which can be huge) into memory.
        Dim bufferLen As Integer = 4096
        Dim buffer As Byte() = New Byte(bufferLen - 1) {}
        Dim bytesRead As Integer

        Do
            ' read a chunk of data from the input file 
            bytesRead = fsIn.Read(buffer, 0, bufferLen)

            ' Decrypt it 
            cs.Write(buffer, 0, bytesRead)
        Loop While bytesRead <> 0

        ' close everything 
        ' this will also close the unrelying fsOut stream 
        cs.Close()
        fsIn.Close()
    End Sub
End Class

6) The result
See the result of encryption and decryption.

Good luck reading that encrypted file

Note: This example uses Rijndael, but there are more methods available in .Net

Encrypt column data in SSIS

Case
I want to encrypt a column with sensitive data. How do I do that in SSIS?

Solutions
A) If your source is a SQL Server table, you could use T-SQL to encrypt your data.
B) An other option is the Script Component with some .Net coding to encrypt your sensitive data.

Note: see this post for encrypting a whole file instead of certain columns.

Let's elaborate that Script Component solution. Let's assume you have an user table that you want to export to a csv file, but you don't want to export the password (or salary) column in plaintext.

Usertable in SQL Server

1) Variable
Create a string variable named EncryptionKey to store your encryption key in and give it a value.

Rightclick in the Control Flow

2) Data Flow Task
Create a new Data Flow Task with a source connection to your table. This example has two columns: Name and Password (see screenshot above step 1).

Data Flow Task with source

3) Script Component
Add a Script Component after the source and choose Transformation. Connect it to the source and give it a suitable name.

Script Component Type Transformation

4) Select Variable
Edit the Script Component and select the variable EncryptionKey from step 1 as a ReadOnlyVariable.

ReadOnlyVariables

5) Input Column
Go to the Input Columns section and select the column that you want to encrypt as a ReadOnly input column.

Readonly input column

6) Output Column
Go to the Inputs and Outputs section and create a new output column to store the excrypted password in. The encypted string will be larger than the original string so make sure it will fit.

New output column named EncryptedPassword

7) The Script
Hit the Edit Script button and remove the PreExecute and PostExecute methods because we don't need them. Copy the code below. I have created two new static methods (Encrypt and Decrypt) that will do the job. They are simplified versions of these examples at CodeProject. If you want to use these scripts commercially, make sure you read the documentation and test it thoroughly! I don't take any responsibility whatsoever.

// C# code
using System;
using System.Data;
using System.IO;                        // Added
using System.Security.Cryptography;     // Added
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
       // Fill the new column with an encrypted version of the password column.
        Row.EncryptedPassword = Encrypt(Row.Password.ToString(), this.Variables.EncryptionKey.ToString());
    }

    // Encrypt text with Rijndael encryption
    public static string Encrypt(string clearText, string Password)
    {
        // Convert password string into byte array
        byte[] clearBytes = System.Text.Encoding.Unicode.GetBytes(clearText);

        // Create Key and IV from the password with salt technique
        PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });

        // Create a symmetric algorithm with Rijndael
        Rijndael alg = Rijndael.Create();

        // Set Key and IV
        alg.Key = pdb.GetBytes(32);
        alg.IV = pdb.GetBytes(16);

        // Create a MemoryStream  
        MemoryStream ms = new MemoryStream();

        // Create a CryptoStream
        CryptoStream cs = new CryptoStream(ms, alg.CreateEncryptor(), CryptoStreamMode.Write);

        // Write the data and make it do the encryption 
        cs.Write(clearBytes, 0, clearBytes.Length);

        // Close CryptoStream
        cs.Close();

        // Get Encypted data from MemoryStream
        byte[] encryptedData = ms.ToArray();

        // return the Encypted data as a String
        return Convert.ToBase64String(encryptedData);
    }

    // Decrypt text with Rijndael encryption
    public static string Decrypt(string cipherText, string Password)
    {
        // Convert password string into byte array
        byte[] cipherBytes = Convert.FromBase64String(cipherText);

        // Create Key and IV from the password with salt technique
        PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });

        // Create a symmetric algorithm with Rijndael
        Rijndael alg = Rijndael.Create();

        // Set Key and IV
        alg.Key = pdb.GetBytes(32);
        alg.IV = pdb.GetBytes(16);

        // Create a MemoryStream  
        MemoryStream ms = new MemoryStream();

        // Create a CryptoStream 
        CryptoStream cs = new CryptoStream(ms, alg.CreateDecryptor(), CryptoStreamMode.Write);

        // Write the data and make it do the decryption 
        cs.Write(cipherBytes, 0, cipherBytes.Length);

        // Close CryptoStream
        cs.Close();

        // Get Decypted data from MemoryStream
        byte[] decryptedData = ms.ToArray();

        // return the Decypted data as a String
        return System.Text.Encoding.Unicode.GetString(decryptedData);
    }
}

Or VB.Net

' VB.Net code
Imports System
Imports System.Data
Imports System.IO                       ' Added
Imports System.Security.Cryptography    ' Added
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Fill the new column with an encrypted version of the password column.
        Row.EncryptedPassword = Encrypt(Row.Password.ToString(), Me.Variables.EncryptionKey.ToString())
    End Sub

    ' Encrypt text with Rijndael encryption
    Public Shared Function Encrypt(ByVal clearText As String, ByVal Password As String) As String
        ' Convert password string into byte array
        Dim clearBytes As Byte() = System.Text.Encoding.Unicode.GetBytes(clearText)

        ' Create Key and IV from the password with salt technique
        Dim pdb As New PasswordDeriveBytes(Password, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, &H65, &H64, &H76, &H65, &H64, &H65, &H76})

        ' Create a symmetric algorithm with Rijndael
        Dim alg As Rijndael = Rijndael.Create()

        ' Set Key and IV
        alg.Key = pdb.GetBytes(32)
        alg.IV = pdb.GetBytes(16)

        ' Create a MemoryStream  
        Dim ms As MemoryStream = New MemoryStream()

        ' Create a CryptoStream
        Dim cs As CryptoStream = New CryptoStream(ms, alg.CreateEncryptor(), CryptoStreamMode.Write)

        ' Write the data and make it do the encryption 
        cs.Write(clearBytes, 0, clearBytes.Length)

        ' Close CryptoStream
        cs.Close()

        ' Get Encypted data from MemoryStream
        Dim encryptedData As Byte() = ms.ToArray()

        ' return the Encypted data as a String
        Encrypt = Convert.ToBase64String(encryptedData)
    End Function

    ' Decrypt text with Rijndael encryption
    Public Shared Function Decrypt(ByVal cipherText As String, ByVal Password As String) As String
        ' Convert password string into byte array
        Dim cipherBytes As Byte() = Convert.FromBase64String(cipherText)

        ' Create Key and IV from the password with salt technique
        Dim pdb As New PasswordDeriveBytes(Password, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, &H65, &H64, &H76, &H65, &H64, &H65, &H76})

        ' Create a symmetric algorithm with Rijndael
        Dim alg As Rijndael = Rijndael.Create()

        ' Set Key and IV
        alg.Key = pdb.GetBytes(32)
        alg.IV = pdb.GetBytes(16)

        ' Create a MemoryStream  
        Dim ms As MemoryStream = New MemoryStream()

        ' Create a CryptoStream 
        Dim cs As CryptoStream = New CryptoStream(ms, alg.CreateDecryptor(), CryptoStreamMode.Write)

        ' Write the data and make it do the decryption 
        cs.Write(cipherBytes, 0, cipherBytes.Length)

        ' Close CryptoStream
        cs.Close()

        ' Get Decypted data from MemoryStream
        Dim decryptedData As Byte() = ms.ToArray()

        ' return the Decypted data as a String
        Decrypt = System.Text.Encoding.Unicode.GetString(decryptedData)
    End Function

End Class

8) The result
Now finish the Data Flow by adding a destination. I added a Data Viewer to see the result.

The result: encrypted data

9) Reverse
By reversing the Data Flow we can decrypt the data.

The result: decrypted data

// C# code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Fill the new column with the decrypted version of the encrypted password column.
    Row.DecryptedPassword = Decrypt(Row.Password.ToString(), this.Variables.EncryptionKey.ToString());
}

Or VB.Net

' VB.Net code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Fill the new column with the decrypted version of the encrypted password column.
    Row.DecryptedPassword = Decrypt(Row.Password.ToString(), Me.Variables.EncryptionKey.ToString())
End Sub

Note: This example uses Rijndael, but there are more methods available in .Net