Showing posts with label handling. Show all posts
Showing posts with label handling. Show all posts

Wednesday, March 21, 2012

Getting row order in Input File when Handling Errors

Hi All,

Well, tha case here is simply that i have a (Suppliers.csv) as an Input.

When taking that file, I do some validation on it's rows (Data type validations, Mandatory Fielda validations..etc).

When some rows to do not meet the requirments i put in these validations , it is supposed to be directed to an (Errors) Table in my SQL DB.

I want to include the order of the invalid row in the input File (The row which did not pass from the pre-mentioned validations) within the (Errors) Table when i direct the invalid rows to it.

Any ideas ?

See the script in my post on surrogate keys.

http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

Place that at the beginning of your data flow to number all rows passing through it. Then in the error flow, you'll have the correct row number.

Sunday, February 26, 2012

Getting hashbytes from XML

Hello
How do you get hash bytes from XML?
checksum() and hashBytes() don't work with XML AS IS. I am handling legal
document. I don't want additional convert like nvarchar or something. I
just want to get the hashbytes from pure XML or stream as is.
Currently I am using CLR, but I think there can be shorter way to do that.
Not only hash but also checksum will do because the computing occurs in safe
place, the DB.
public static byte[] toHash(SqlXml sqx)
{
XmlDocument doc=new XmlDocument();
doc.Load(sqx.CreateReader());
MemoryStream ms = new MemoryStream();
doc.Save(ms);
ms.Position = 0;
BinaryReader br = new BinaryReader(ms);
byte[] bs = br.ReadBytes(Int32.Parse(ms.Length.ToString()));
SHA1Managed sha = new SHA1Managed();
byte[] hash = sha.ComputeHash(bs);
return (hash);
}
This morning I got smarter and shortened the code into 3 lines.

> public static byte[] toHash(SqlBytes sbs)
> {
> SHA1Managed sha = new SHA1Managed();
> byte[] hash = sha.ComputeHash(sbs);
> return (hash);
> }
Callere is dbo.toHash(convert(varbinary(max), @.xml))
I am even considering remove the CLR. Then the code may be,
hashBytes('sha1', convert(varbinary(max), @.xml))
But I discovered dotnet sha1 and SQL Server sha1 return different result.
Even the lengths of the results are different. I prefered dotnet, but I am
studying still, and any recommandation will be appreciated.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23vHtSwwkHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hello
> How do you get hash bytes from XML?
> checksum() and hashBytes() don't work with XML AS IS. I am handling legal
> document. I don't want additional convert like nvarchar or something. I
> just want to get the hashbytes from pure XML or stream as is.
> Currently I am using CLR, but I think there can be shorter way to do that.
> Not only hash but also checksum will do because the computing occurs in
> safe place, the DB.
> public static byte[] toHash(SqlXml sqx)
> {
> XmlDocument doc=new XmlDocument();
> doc.Load(sqx.CreateReader());
> MemoryStream ms = new MemoryStream();
> doc.Save(ms);
> ms.Position = 0;
> BinaryReader br = new BinaryReader(ms);
> byte[] bs = br.ReadBytes(Int32.Parse(ms.Length.ToString()));
> SHA1Managed sha = new SHA1Managed();
> byte[] hash = sha.ComputeHash(bs);
> return (hash);
> }
>

Getting hashbytes from XML

Hello
How do you get hash bytes from XML?
checksum() and hashBytes() don't work with XML AS IS. I am handling legal
document. I don't want additional convert like nvarchar or something. I
just want to get the hashbytes from pure XML or stream as is.
Currently I am using CLR, but I think there can be shorter way to do that.
Not only hash but also checksum will do because the computing occurs in safe
place, the DB.
public static byte[] toHash(SqlXml sqx)
{
XmlDocument doc=new XmlDocument();
doc.Load(sqx.CreateReader());
MemoryStream ms = new MemoryStream();
doc.Save(ms);
ms.Position = 0;
BinaryReader br = new BinaryReader(ms);
byte[] bs = br.ReadBytes(Int32.Parse(ms.Length.ToString()));
SHA1Managed sha = new SHA1Managed();
byte[] hash = sha.ComputeHash(bs);
return (hash);
}This morning I got smarter and shortened the code into 3 lines.

> public static byte[] toHash(SqlBytes sbs)
> {
> SHA1Managed sha = new SHA1Managed();
> byte[] hash = sha.ComputeHash(sbs);
> return (hash);
> }
Callere is dbo.toHash(convert(varbinary(max), @.xml))
I am even considering remove the CLR. Then the code may be,
hashBytes('sha1', convert(varbinary(max), @.xml))
But I discovered dotnet sha1 and SQL Server sha1 return different result.
Even the lengths of the results are different. I prefered dotnet, but I am
studying still, and any recommandation will be appreciated.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23vHtSwwkHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hello
> How do you get hash bytes from XML?
> checksum() and hashBytes() don't work with XML AS IS. I am handling legal
> document. I don't want additional convert like nvarchar or something. I
> just want to get the hashbytes from pure XML or stream as is.
> Currently I am using CLR, but I think there can be shorter way to do that.
> Not only hash but also checksum will do because the computing occurs in
> safe place, the DB.
> public static byte[] toHash(SqlXml sqx)
> {
> XmlDocument doc=new XmlDocument();
> doc.Load(sqx.CreateReader());
> MemoryStream ms = new MemoryStream();
> doc.Save(ms);
> ms.Position = 0;
> BinaryReader br = new BinaryReader(ms);
> byte[] bs = br.ReadBytes(Int32.Parse(ms.Length.ToString()));
> SHA1Managed sha = new SHA1Managed();
> byte[] hash = sha.ComputeHash(bs);
> return (hash);
> }
>