One of my latest task is to encrypt data using RSA so instead of spending my time building a new service, I create a package and added the below code to the pipeline and BOOM...
Task completed.
enjoy...
#region Help: Introduction to the Script Component
/* The Script Component allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services data flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script component. */
#endregion
#region Namespaces
using System;
using System.Data;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Security.Cryptography;
using System.Text;
#endregion
/// <summary>
/// This is the class to which to add your code. Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Help: Using Integration Services variables and parameters
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script component, according to whether or not your
* code needs to write into the variable. To do so, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
* Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable or parameter:
* DateTime startTime = Variables.MyStartTime;
*
* Example of writing to a variable:
* Variables.myStringVariable = "new value";
*/
#endregion
#region Help: Using Integration Services Connnection Managers
/* Some types of connection managers can be used in this script component. See the help topic
* "Working with Connection Managers Programatically" for details.
*
* To use a connection manager in this script, first ensure that the connection manager has
* been added to either the list of connection managers on the Connection Managers page of the
* script component editor. To add the connection manager, save this script, close this instance of
* Visual Studio, and add the Connection Manager to the list.
*
* If the component needs to hold a connection open while processing rows, override the
* AcquireConnections and ReleaseConnections methods.
*
* Example of using an ADO.Net connection manager to acquire a SqlConnection:
* object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
* SqlConnection salesDBConn = (SqlConnection)rawConnection;
*
* Example of using a File connection manager to acquire a file path:
* object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
* string filePath = (string)rawConnection;
*
* Example of releasing a connection manager:
* Connections.SalesDB.ReleaseConnection(rawConnection);
*/
#endregion
#region Help: Firing Integration Services Events
/* This script component can fire events.
*
* Example of firing an error event:
* ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
*
* Example of firing an information event:
* ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
*
* Example of firing a warning event:
* ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
*/
#endregion
/// <summary>
/// This method is called once, before rows begin to be processed in the data flow.
///
/// You can remove this method if you don't need to do anything here.
/// </summary>
public override void PreExecute()
{
base.PreExecute();
/*
* Add your code here
*/
}
/// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
base.PostExecute();
/*
* Add your code here
*/
}
/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
/// string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
/// Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
* Add your code here
*/
}
// Going to encrypt the pipeline as it comes.
public override void ProcessInput(int InputID, PipelineBuffer Buffer)
{
String publicPrivateKey = this.Variables.strPublicPrivateKey.ToString();
String publicKey = this.Variables.strPublicKey.ToString();
int index = 0;
String DataBackup = "";
while (Buffer.NextRow())
{
try
{
for (int columnIndex = 0;
columnIndex < Buffer.ColumnCount;
columnIndex++)
{
index = columnIndex;
string columnData = null;
if (Buffer.IsNull(columnIndex))
{
columnData = "is NULL";
}
else
{
BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);
switch (columnInfo.DataType)
{
case DataType.DT_WSTR:
case DataType.DT_STR:
columnData += Buffer.GetString(columnIndex);
DataBackup = columnData;//Save backup
columnData = RSAToColumnEncrypt(columnData, publicKey);
Buffer.SetString(columnIndex, columnData);
index = 0; //Reset index;
DataBackup = ""; //Reset data
break;
// add code to support more data types here
default:
columnData = "";
index = 0; //Reset index;
DataBackup = ""; //Reset data
break;
}
}
}
}
catch
{
Buffer.SetString(index, DataBackup);
index = 0;
DataBackup = "";
}
}
base.ProcessInput(InputID, Buffer);
}
static public String RSAToColumnEncrypt (String ColumnData,String EncrKey)
{
try
{
//Create a UnicodeEncoder to convert between byte array and string.
// UnicodeEncoding ByteConverter = new UnicodeEncoding();
//Create byte arrays to hold original, encrypted, and decrypted data.
byte[] dataToEncrypt = System.Text.Encoding.UTF8.GetBytes(ColumnData);// Plain Text
byte[] encryptedData;
//Create a new instance of RSACryptoServiceProvider to generate
//public and private key data.
using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
{
RSA.FromXmlString(EncrKey); // reference public key
//Pass the data to ENCRYPT, the public key information
//(using RSACryptoServiceProvider.ExportParameters(false),
//and a boolean flag specifying no OAEP padding.
encryptedData = RSAEncrypt(dataToEncrypt, RSA.ExportParameters(false), false);
String encryptedString = Convert.ToBase64String(encryptedData);
return encryptedString;
//Pass the data to DECRYPT, the private key information
//(using RSACryptoServiceProvider.ExportParameters(true),
//and a boolean flag specifying no OAEP padding.
//decryptedData = RSADecrypt(encryptedData, RSA.ExportParameters(true), false);
//Display the decrypted plaintext to the console.
//Console.WriteLine("Decrypted plaintext: {0}", ByteConverter.GetString(decryptedData));
}
}
catch (ArgumentNullException)
{
//Catch this exception in case the encryption did
//not succeed.
return "Encryption failed.";
}
}
static public String RSAToColumnDecrypt(String EncrypData,String privatekey)
{
try
{
byte[] EncryptArray = Convert.FromBase64String(EncrypData);
byte[] DecryptArray;
using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
{
RSA.FromXmlString(privatekey);
DecryptArray = RSADecrypt(EncryptArray, RSA.ExportParameters(true), false);
return Encoding.UTF8.GetString(DecryptArray);
}
}
catch
{
//Catch this exception in case the encryption did
//not succeed.
return "Decrypt failed.";
}
}
static public byte[] RSAEncrypt(byte[] DataToEncrypt, RSAParameters RSAKeyInfo, bool DoOAEPPadding)
{
try
{
byte[] encryptedData;
//Create a new instance of RSACryptoServiceProvider.
using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
{
//Import the RSA Key information. This only needs
//toinclude the public key information.
RSA.ImportParameters(RSAKeyInfo);
//Encrypt the passed byte array and specify OAEP padding.
//OAEP padding is only available on Microsoft Windows XP or
//later.
encryptedData = RSA.Encrypt(DataToEncrypt, DoOAEPPadding);
}
return encryptedData;
}
//Catch and display a CryptographicException
//to the console.
catch (CryptographicException e)
{
Console.WriteLine(e.Message);
return null;
}
}
static public byte[] RSADecrypt(byte[] DataToDecrypt, RSAParameters RSAKeyInfo, bool DoOAEPPadding)
{
try
{
byte[] decryptedData;
//Create a new instance of RSACryptoServiceProvider.
using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
{
//Import the RSA Key information. This needs
//to include the private key information.
RSA.ImportParameters(RSAKeyInfo);
//Decrypt the passed byte array and specify OAEP padding.
//OAEP padding is only available on Microsoft Windows XP or
//later.
decryptedData = RSA.Decrypt(DataToDecrypt, DoOAEPPadding);
}
return decryptedData;
}
//Catch and display a CryptographicException
//to the console.
catch (CryptographicException e)
{
Console.WriteLine(e.ToString());
return null;
}
}
}
