Last year we (colleague Marc Potters and me) created a custom Checksum Transformation for SSIS. We use it a lot to compare records from two sources. Instead of comparing a whole bunch of columns in a very large (unreadable and unmaintainable) expression we just compare the hash of both records.
Someone asked me if it was possible to add this custom transformation via BIML. The documentation and examples for custom transformations in BIML are a little limited and of course different for each one.
Solution
Make sure you install the Checksum Transformation. This BIML script uses version 1.3 of the Checksum Transformation in SSIS 2012 and BIDS Helper version 1.6.4. If you use an other version of Checksum or SSIS, then the ComponentClassId, ComponentTypeName and TypeConverter properties will probably have a different GUID or PublicKeyToken. By creating a package manually and viewing the source code you can find the correct values.
Some browsers don't show capitals in the xml above, but you can download the BIML Script here.SELECT AddressLine1, AddressLine2, City FROM Address 0 Salt123 |
Note 1: If you want to use a variable for the Salt, then you need to know the GUID of the variable. Create a variable in BIML, but with a GUID and use this GUID as Salt_Variable. See step 3 of this blog post.
Note 2: Don't change the names of the InputPath and OutputPath. The transformation is expecting these names.
Hi ,
ReplyDeleteI am working on BIML Script component, where I am taking data from OLEDB Source.
In Script Component I want all the input columns to be checked by default as input.
I have no idea how to proceed for the same.
Below is my code :-
-->
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("SC_Example.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Ciber Nederland")]
[assembly: AssemblyProduct("SC_Example.csproj")]
[assembly: AssemblyCopyright("Copyright @ Ciber Nederland 2012")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.*")]
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;
using System.IO;
using System.Reflection;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
string strColumnsValue = string.Empty;
Type rowType = Row.GetType();
PropertyInfo columnProperty;
MD5 md5 = new MD5CryptoServiceProvider();
Encoder enc = System.Text.Encoding.Unicode.GetEncoder();
foreach(IDTSInputColumn100 Rw in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
{
columnProperty = rowType.GetProperty(Rw.Name);
strColumnsValue += Convert.ToString(columnProperty.GetValue(Row,null));
}
byte[] bbb = new byte[strColumnsValue.Length * 3];
bbb = UnicodeEncoding.Unicode.GetBytes(strColumnsValue);
byte[] hash = md5.ComputeHash(bbb);
String strHash = Convert.ToBase64String(hash);
Row.RowChecksum = strHash;
}
}
Regards,
Vipin jha
Aks your (ex-)colleague John Minkjan or take a look at the InputBuffer Columns.
ReplyDelete