How to Encrypt and Decrypt CSV File in SSIS

By:   |   Updated: 2022-05-11   |   Comments (1)   |   Related: More > Integration Services Development


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

Problem

You have a SSIS Package that extracts data from a SQL Server database and saves it to a csv file for your customer to pick up at a secure SFTP location. Now your customer asks you to encrypt the file before you drop it in the SFTP location as it is sensitive data. Learn how to encrypt and decrypt the data using SSIS in this article.

Solution

In this tip I am going to build on the previous tip, How to export data to a csv file using a SSIS package, and make a few small changes to ensure the data get saved in an encrypted file. We will be working with a Microsoft Namespace named System.Security.Cryptography.

Setting up the Environment

Open the solution created in the previous tip with Visual Studio, in my case it was created with VS 2019.

On the Control Flow tab, there is a Data Flow Task, which if you double-click on its icon will open the Data Flow tab. On the Data Flow tab, you will find an OLE DB Source which is extracting the data from SQL Server. Also on the Data Flow Task, you will find a Flat File Destination which is using the Flat File Connection Manager to save the data in a csv file on a location on your hard drive, in the example: C:\Tmp\SSIS\

Back to the Control Flow tab, there are 2 File System Tasks:

  • The first one: File System Task - Change Filename, to change the filename to a unique filename
  • The second on: File System Task - Delete File, to delete the originally saved file

See the Control Flow and the Data Flow tabs in the images below.

To encrypt the csv file, we will just add a Script Task to the Control Flow tab and reroute the data from the Data Flow Task to the Script Task, then to the File System Task - Change Filename, and then to the File System Task - Delete File.

We will also add a new File System Task - Delete File 1 to delete the extra encrypted file.

Make a copy of the original package that was created to extract the data by right-clicking on the package name and then select Copy, then right-click on the SSIS Packages and select Paste which should create a copy of your original package.

Rename the package to a suitable name, in my case I will just use a number increment.

Encrypting a File with SSIS

Now we can work on the newly copied package and leave the original intact. Make sure the package that we are going to change is open in the designer area.

Add 2 new Variables to the list of Variables, right-click on the designer area and select Variables to open up the Variables windows as shown below, and add the variables:

  • EncryptFilename: C:\Tmp\SSIS\Employees_ENCRYPTED.csv
  • EncryptionKey: abc - any value that you want to use to encrypt the file with

See all the variables in the image below.

Drag and drop a Script Task onto the designer area next to the Data Flow Task.

Change the route of the data flow as in the below image.

Double-click on the Script Task icon to open the Script Task Editor as in the image below.

Change the ReadOnlyVariables to: User::EncryptionKey

Next, click on the Edit Script button where you will be adding some code to do the actual encryption.

You need to add some code to the solution to do the encryption. See the code below that you need to add. See images below with changes and the code below the images. Also add the Encrypt method below the Main() method as in the image above.

Here is the code:

 #region Namespaces 
 using System; 
 using System.Data; 
 using System.IO; // Added 
 using System.Security.Cryptography; // Added 
 using Microsoft.SqlServer.Dts.Runtime; 
 using System.Windows.Forms; 
 #endregion 
   
 namespace ST_7a6b2bd79623420ea1faebb34975c685 
 { 
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] 
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase 
 { 
 public void Main() 
 { 
 string filepath = Dts.Connections["Flat File Connection Manager"].ConnectionString; 
   
 string newFilepath = filepath.Substring(0, filepath.LastIndexOf(".")) + "_ENCRYPTED" + filepath.Substring(filepath.LastIndexOf(".")); 
   
 string encryptionKey = Dts.Variables["EncryptionKey"].ToString(); 
   
 Encrypt(filepath, newFilepath, encryptionKey); 
   
 Dts.TaskResult = (int)ScriptResults.Success; 
 } 
   
 public static void Encrypt(string fileIn, string fileOut, string Password) 
 { 
 FileStream fsIn = new FileStream(fileIn, FileMode.Open, FileAccess.Read); 
 FileStream fsOut = new FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write); 
 PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 }); 
 Rijndael alg = Rijndael.Create(); 
 alg.Key = pdb.GetBytes(32); 
 alg.IV = pdb.GetBytes(16); 
 CryptoStream cs = new CryptoStream(fsOut, alg.CreateEncryptor(), CryptoStreamMode.Write); 
 int bufferLen = 4096; 
 byte[] buffer = new byte[bufferLen]; 
 int bytesRead; 
   
 do 
 { 
 bytesRead = fsIn.Read(buffer, 0, bufferLen); 
 cs.Write(buffer, 0, bytesRead); 
 } while (bytesRead != 0); 
 cs.Close(); 
 fsIn.Close(); 
 } 
   
 #region ScriptResults declaration 
 enum ScriptResults 
 { 
 Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, 
 Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure 
 }; 
 #endregion 
 } 
 }  
 

Just save the Solution and close it and click on the OK button of the Script Task Editor to close the Editor.

Make the following change as in the image below to the File System Task Editor:

Change the File System Task Editor - Delete File as follows:

Copy and paste the File System Task Editor - Delete File and name it the same with a 1 at the end with the following change:

Your Control Flow tab should now look as in the image below.

Now you are ready to run the package and see the output in the File Explorer.

When the package runs, it should look as in the image below. Open a File Explorer using Preview on the right-side and then right click on the file created to see the contents of the csv file that was encrypted.

Decrypting a File with SSIS

When you have encrypted a file and when you need to use it you will need to decrypt the file. We will work through the steps to decrypt the file that was encrypted in the steps above.

Setting up the Environment

Create a new package by right-clicking on the SSIS Packages and the click on New SSIS Package

Open the New Package by Double-clicking on the newly created package and the add a Script Task to the designer area

Create the following 3 Variables:

  • DecryptionKey: abc
  • DecryptedFilename: C:\Tmp\SSIS\Employees.csv
  • EncryptedFilename: C:\Tmp\SSIS\Employees_ENCRYPTED.csv

Create a new Flat File Connection Manager and make the changes as in the image below.

Double-click on the Script Task and change the ReadOnlyVariables as in the image below.

Click on the Edit Script button to open up the script editor that will handle the Decryption part of the file.

Below is the code:

 #region Namespaces 
 using System; 
 using System.Data; 
 using System.IO; // Added 
 using System.Security.Cryptography; // Added 
 using Microsoft.SqlServer.Dts.Runtime; 
 using System.Windows.Forms; 
 #endregion 
   
 public void Main() 
 { 
 // the name of the connection manager. 
 string filepath = Dts.Connections["Flat File Connection Manager"].ConnectionString; 
   
 string newFilepath = filepath.Substring(0, filepath.LastIndexOf(".")) + "_ENCRYPTED" + filepath.Substring(filepath.LastIndexOf(".")); 
   
 string encryptionKey = Dts.Variables["EncryptionKey"].ToString(); 
   
 // Encrypt(filepath, newFilepath, encryptionKey); 
   
 Decrypt(newFilepath, newFilepath.Replace("_ENCRYPTED", "_DECRYPTED"), encryptionKey); 
   
 Dts.TaskResult = (int)ScriptResults.Success; 
 } 
 public static void Decrypt(string fileIn, string fileOut, string Password) 
 { 
 FileStream fsIn = new FileStream(fileIn, FileMode.Open, FileAccess.Read); 
 FileStream fsOut = new FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write); 
 PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 }); 
 Rijndael alg = Rijndael.Create(); 
 alg.Key = pdb.GetBytes(32); 
 alg.IV = pdb.GetBytes(16); 
 CryptoStream cs = new CryptoStream(fsOut, alg.CreateDecryptor(), CryptoStreamMode.Write); 
 int bufferLen = 4096; 
 byte[] buffer = new byte[bufferLen]; 
 int bytesRead; 
   
 do 
 { 
 bytesRead = fsIn.Read(buffer, 0, bufferLen); 
   
 cs.Write(buffer, 0, bytesRead); 
 } while (bytesRead != 0); 
 cs.Close(); 
 fsIn.Close(); 
 } 
 

Save and Close the instance of Visual Studio and click on the OK button of the Script Task Editor

Next, you can run the task and see the decrypted file in the File Explorer.

Next Steps
  • You can work through a complete SSIS Tutorial on the MSSQTips.com website.



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights








About the author

Jan Potgieter has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies.



View all my tips


Article Last Updated: 2022-05-11

Comments For This Article




Wednesday, May 11, 2022 - 8:20:34 AM - luther atkinsonBack To Top(90079)
What method or methods would a non-Microsoft platform need to use when decrypting the file? Or is this something that would only work when exchanging files between Microsoft platforms?