<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>
<#@ import namespace="System.IO"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Annotations>
<Annotation>
File: MD5.ScriptComponent.biml
Demo biml using the ScriptComponent to
create an MD5 hash.
Enviroment:
DB: MS-SQL2012
BIML: 1.6 VS2010 BIDS Helper
(c) John Minkjan biml101.blogspot.com
</Annotation>
</Annotations>
<Connections>
<OleDbConnection
Name="CnOleDBAdventureWorks2012"
ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"/>
</Connections>
<ScriptProjects>
<ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SC Add MD5">
<AssemblyReferences>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
<AssemblyReference AssemblyPath="System.dll" />
<AssemblyReference AssemblyPath="System.AddIn.dll" />
<AssemblyReference AssemblyPath="System.Data.dll" />
<AssemblyReference AssemblyPath="System.Xml.dll" />
</AssemblyReferences>
<Files>
<File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Ciber Nederland")]
[assembly: AssemblyProduct("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
[assembly: AssemblyCopyright("Copyright @ Ciber Nederland 2012")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.*")]
</File>
<File Path="ScriptMain.cs">
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.IO;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
static public string GetMd5Sum(string str)
{
Encoder enc = System.Text.Encoding.Unicode.GetEncoder();
byte[] unicodeText = new byte[str.Length * 2];
enc.GetBytes(str.ToCharArray(), 0, str.Length, unicodeText, 0, true);
MD5 md5 = new MD5CryptoServiceProvider();
byte[] result = md5.ComputeHash(unicodeText);
StringBuilder sb = new StringBuilder();
for (int i = 0; i != result.Length; i++)
{
sb.Append(result[i].ToString("X2"));
}
return sb.ToString();
}
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.MD5AllColumns = GetMd5Sum(Row.FirstName + Row.LastName);
}
}
</File>
</Files>
<InputBuffer Name="Input0">
<Columns>
<Column Name="MD5AllColumns" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
<Column Name="FirstName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
<Column Name="LastName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
</Columns>
</InputBuffer>
<OutputBuffers>
<OutputBuffer Name="Output0">
</OutputBuffer>
</OutputBuffers>
</ScriptComponentProject>
</ScriptProjects>
<Packages>
<Package ConstraintMode="Parallel" Name="MD5.ScriptComponent">
<Variables>
<Variable Name ="RowCount"
DataType="Int32"
EvaluateAsExpression="false">0</Variable>
<!--Stock symbol has to be enclosed in double quotes-->
</Variables>
<Tasks>
<Container ConstraintMode ="Parallel" Name="SEQ Main">
<Tasks>
<Dataflow Name="DFT Main">
<Transformations>
<OleDbSource Name="ODS Adventureworks Person" ConnectionName="CnOleDBAdventureWorks2012">
<ExternalTableInput Table = "[Person].[Person]"></ExternalTableInput>
</OleDbSource>
<DerivedColumns Name="DC MD5">
<InputPath OutputPathName="ODS Adventureworks Person.Output"></InputPath>
<Columns>
<Column Name="MD5AllColumns" DataType="AnsiString" Length="50">"--"</Column>
</Columns>
</DerivedColumns>
<ScriptComponentTransformation Name="SC Add MD5">
<InputPath OutputPathName="DC MD5.Output"></InputPath>
<ScriptComponentProjectReference ScriptComponentProjectName="SC Add MD5" />
</ScriptComponentTransformation>
<!-- Dummy Rowcount to show results in dataviewer -->
<RowCount VariableName="User.RowCount" Name="RC Input">
<InputPath OutputPathName="SC Add MD5.Output0"></InputPath>
</RowCount>
</Transformations>
</Dataflow>
</Tasks>
</Container>
</Tasks>
</Package>
</Packages>
</Biml>
Will get you:
Till Next Time
Hi John, thank you for taking the time to do this. Do you by any chance have the sample package? I am trying to achieve the same result but the BIML script for the hash is failing.
ReplyDelete