By: Jan Potgieter | Updated: 2022-05-11 | Comments (2) | Related: More > Integration Services Development
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()
{
// Filepath of file that needs to be encrypted that is the name of the connection manager
string filepath = Dts.Connections["Flat File Connection Manager"].ConnectionString;
// New filename: C:\Tmp\SSIS\file.csv => C:\Tmp\SSIS\file_ENCRYPTED.csv
string newFilepath = filepath.Substring(0, filepath.LastIndexOf(".")) + "_ENCRYPTED" + filepath.Substring(filepath.LastIndexOf("."));
// Password from SSIS variable
string encryptionKey = Dts.Variables["EncryptionKey"].ToString();
// Encrypted the file
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()
{
// Get the filepath of the file that needs to be encrypted. This is
// the name of the connection manager.
string filepath = Dts.Connections["Flat File Connection Manager"].ConnectionString;
// Determine new name: d:\folder\file.csv => d:\folder\file_ENCRYPTED.csv
string newFilepath = filepath.Substring(0, filepath.LastIndexOf(".")) + "_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;
}
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.
About the author
Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-05-11
