MD5 Script Component

gravatar

John Minkjan

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/

published 09.17.12

last updated 09.17.12


Share

Tags

  • BIML
  • MD5
  • ScriptComponent
                            


<#@ 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

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

There are no comments yet.