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
