convert all the column of table to ToBase64String
-
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 -
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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 -
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
-
What is CLR ?
-
vipin_jha123 (2/18/2015)
What is CLR ?How hard did you try?
How about this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply