Add derived column to table via c # script in SSIS
I would like to compute a hash function (using MD5) and add a column to an existing table with the result. I am using a script task in SSIS to write a short C # script. Here's my script:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Security.Cryptography;
using System.Text;
using System.Windows.Forms;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
* Add your code here
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
* Add your code here
*/
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
* Add your code here
*/
using (MD5 md5Hash = MD5.Create())
{
string hash = GetMd5Hash(md5Hash, Row);
MessageBox.Show(hash);
}
}
static string GetMd5Hash(MD5 md5Hash, Input0Buffer input)
{
// Convert the input string to a byte array and compute the hash.
byte[] data = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input.ToString()));
// Create a new Stringbuilder to collect the bytes
// and create a string.
StringBuilder sBuilder = new StringBuilder();
// Loop through each byte of the hashed data
// and format each one as a hexadecimal string.
for (int i = 0; i < data.Length; i++)
{
sBuilder.Append(data[i].ToString("x2"));
}
// Return the hexadecimal string.
return sBuilder.ToString();
}
}
My SSIS package looks like this: I just grab a table from the database in one line. I would like to hash all columns, create another column and store the hash result there. I can generate a hash, but I don't know how to add a column to the result set and insert the hash value into that column. Any help would be greatly appreciated. Thank.
+3
source to share
1 answer
To assign a column in C # as you requested in response to billinkc, your code would look like this:
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
* Add your code here
*/
using (MD5 md5Hash = MD5.Create())
{
//string hash = GetMd5Hash(md5Hash, Row);
Row.MyOutputColumn = GetMd5Hash(md5Hash, Row);
//MessageBox.Show(hash);
}
}
+1
source to share