Saturday, July 3, 2010

Overcome Slow Performance in SSIS with SQL FOR XML Clause

SQL Server Integration Services has a significant performance issue with large result sets returned by SQL'ss FOR XML clause. The problem is due to memory consumption and the way SSIS's data flow engine handles row-based results. When SSIS receives a data stream, it buffers a portion of the stream, performs the necessary operations on the rows in the buffer, clears the buffer, and repeats the process until there is no more data remaining in the stream. The problem with the way FOR XML returns data is that it doesn't return rows, but rather a result set with a single row, which is seen by SSIS as a large Binary Large Object ( BLOB).

The initial reaction many developers have to this issue is to decrease the DefaultBufferSize property of data flow task. Unfortunately, this won't help at all since the buffer is not able work with fractional rows, which means the buffer(s) will continue to fill beyond their threshold until a whole number of rows is reached. With a single row, this means the entire stream will be loaded into memory. SQL 2005 and above provide a solution to this problem with the ability to shred the xml data type into individual rows. Compare the result returned by the below queries.

DECLARE @XML xml

SET @XML = (SELECT
UserID,
FirstName,
LastName
FROM
Users
FOR XML PATH('User'))
SELECT @XML AS UserXML

results with a single row

DECLARE @XML xml

SET @XML = (SELECT
UserID,
FirstName,
LastName
FROM
Users
FOR XML PATH('User'))


SELECT nref.query('.') AS UserXML
from @XML.nodes('//User') AS R(nref)

results with multiple rows SSIS can efficiently buffer the result set returned by the second query, which will enable large sets to be processed without issue.

Encrypting a stored procedure in SQL Server 2005

It is possible to encrypt the text of stored procedures
containing sensitive information. SQL Server provides the WITH
ENCRYPTION to encrypt the text of the stored procedure.

CREATE procedure [dbo].[spLoginCheck]
WITH ENCRYPTION AS
SET ROWCOUNT 10
SELECT Products.UserName AS UN
FROM Users


Once the stored procedure has been created WITH ENCRYPTION,
an attempts to view the stored procedure returns a message specifying that the text is encrypted as below,

"EXEC sp_helptext usp_SEL_EmployeePayHistory"

'The text for object 'spLoginCheck' is encrypted.'

Please be cautious to save the original text of the stored procedure before encrypting it,
as there is no straightforward way to decode the encrypted sp. One alternate is to attach a
debugger to the server process and retrieve the decrypted procedure from memory at runtime.

Friday, July 2, 2010

Simple Encryption and Decryption

public class EnDC
{

/*
The .net 2.0 framework contains classes for encryption which can be found in the
System.Security.Cryptography namespace. These classes overcomes the complexity of
the encryption as under most scenarios, a developer simply wants a function a clear
text string as a parameter and returns an encrypted string.
The C# code for this function is below
*/

public string EncryptString(string ClearText)
{

byte[] clearTextBytes = Encoding.UTF8.GetBytes(ClearText);

System.Security.Cryptography.SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();

MemoryStream ms = new MemoryStream();
byte[] rgbIV = Encoding.ASCII.GetBytes("kolpfmckfryjgmkk");
byte[] key = Encoding.ASCII.GetBytes("kokollmnlvita345dfgniskhanmumccc");
CryptoStream cs = new CryptoStream(ms, rijn.CreateEncryptor(key, rgbIV),
CryptoStreamMode.Write);

cs.Write(clearTextBytes, 0, clearTextBytes.Length);

cs.Close();

return Convert.ToBase64String(ms.ToArray());
}

/*Following is the code of Decrypting the given string. */

public string DecryptString(string EncryptedText)
{
byte[] encryptedTextBytes = Convert.FromBase64String(EncryptedText);

MemoryStream ms = new MemoryStream();

System.Security.Cryptography.SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();


byte[] rgbIV = Encoding.ASCII.GetBytes("kolpfmckfryjgmkk");
byte[] key = Encoding.ASCII.GetBytes("kokollmnlvita345dfgniskhanmumccc");

CryptoStream cs = new CryptoStream(ms, rijn.CreateDecryptor(key, rgbIV),
CryptoStreamMode.Write);

cs.Write(encryptedTextBytes, 0, encryptedTextBytes.Length);

cs.Close();

return Encoding.UTF8.GetString(ms.ToArray());

}

/*Please be sure to replace the encryption keys I provided above with your
own unique values of the same length.
Also, note that the encryption type used above is symmetric t.
If you need an asymmetric or hash
algorithm use the appropriate class within the Cryptography namespace*/
}