convert all the column of table to ToBase64String

Question

Post reply

convert all the column of table to ToBase64String

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

Ok, I would ask why the change from SSIS to SQL.

IN SQL check out the HASHBYTES function.

eg

SELECT

COL1,COL2, HASHBYTES('MD5',COL1+'|'+COL2+'|')

FROM aTable

One other thing there's no guarantee that you will get the same Value as per the previous solution that was implemented.

The only way to be sure is to keep using the old method.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Phil Parkin SSC Guru Points: 246978 More actions · Answer 2

Jason-299789 (2/18/2015)
Ok, I would ask why the change from SSIS to SQL.
IN SQL check out the HASHBYTES function.
eg
SELECT
COL1,COL2, HASHBYTES('MD5',COL1+'|'+COL2+'|')
FROM aTable
One other thing there's no guarantee that you will get the same Value as per the previous solution that was implemented.
The only way to be sure is to keep using the old method.

Perhaps a suitable CLR function can be created to do the same as what was being done in SSIS.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

Interesting Idea Phil, a CLR would certainly work, but the question is would the company allow a CLR to be placed on the server.

I know a few companies are very funny about such things.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 4

Clients having 100 of ssis package for every table they are having separate ssis package.

Now they wanted to have single package with single DFT which can load all the source to destination as per the input.

I did using BIML but clients told me that BIML need license so I dropped that plan

Now I have created SP which is working fine and full fill the clients requirement.

Now my concern is in production environment rowchecksum value is different coming than what i am getting using sql.

Currently SSIS using script component to convert the source column and give the rowcheckusm value.

SSIS Code:-

Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014)

Dim hash As Byte() = md5.ComputeHash(columnContents)

Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)

Row.RowChecksum = hashString

End Sub

I also want to have same checksum value using sql

regards,

Vipin jha

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 5

vipin_jha123

Hall of Fame

Points: 3085

February 18, 2015 at 4:52 am

#1778302

What is CLR ?

Phil Parkin SSC Guru Points: 246978 More actions · Answer 6

vipin_jha123 (2/18/2015)
What is CLR ?

How hard did you try?

How about this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 7

Done a bit of digging here, using a SQLCLR equivalent to the OPs script (source below)

using System;

using System.Data.SqlTypes;

using System.Text;

using System.Security.Cryptography; // Will need a reference to System.Security

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]

public static String ToBase64String(String str)

{

if (str == null)

{

return str;

}

MD5 md5 = new MD5CryptoServiceProvider();

byte[] columnContents = UnicodeEncoding.Unicode.GetBytes(str);

byte[] hash = md5.ComputeHash(columnContents);

String hashString = Convert.ToBase64String(hash, Base64FormattingOptions.None);

return hashString;

}

CREATE FUNCTION [dbo].[ToBase64String](@str [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [MySQLCLR].[UserDefinedFunctions].[ToBase64String]

gives the same results as using the built-in HASHBYTES and XML conversion to base64

DECLARE @STR NVARCHAR(100);

SET @STR='abcdefg';

SELECT dbo.ToBase64String(@str);

WITH source(col) AS (

SELECT HASHBYTES('MD5',@str))

SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("col")))', 'VARCHAR(MAX)') AS Base64String

FROM source;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537