<#@ 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>
Demo biml using the ScriptComponent to create an MD5 hash, very handy if you are not allowed to use open source altenatives like ==> http://ssismhash.codeplex.com/ Also published on http://biml101.blogspot.nl/2012/09/biml-md5-scriptcomponent-hash.html
Comments
There are no comments yet.