Encrypting field before storing in destination | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Encrypting field before storing in destination

A DTS is reading fields from a source database using a stored procedure. Before storing certain fields in the (different) destination database, they need to be encrypted. I’ve created a function that will perform the encryption as dbo.ud_encryptField(SSN). Within the DTS, how may I call this function within the VB transformation script? Here’s what it starts as: Function Main()
DTSDestination("SSN") = DTSSource("SSN")
Main = DTSTransformStat_OK
End Function May I simply use DTSDestination("SSN") = dbo.ud_encryptField( DTSSource("SSN") ) ?
For the sake of performance I would suggest to perform encryption after you have imported the data from the source, not sure how you can call this from Activex or may be refer to relevant VBscript forums in thsi case to accomplish the encryption task. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
ActiveX was a bit unfamiliar, so I ended up writing two encryption functions on the source server and performing decryption and then immediate re-encryption there inside a stored procedure. xpcrypt is surprisingly fast, so I see no significant performance hits.
Glad to hear the feedback, similar to this on your other post you could perform the same if performance isn’t a factore there. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>