SQL Server Performance

Encrypting field before storing in destination

Discussion in 'SQL Server DTS-Related Questions' started by bryan42, Jun 29, 2007.

  1. bryan42 New Member

    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") ) ?
  2. satya Moderator

    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.
  3. bryan42 New Member

    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.
  4. satya Moderator

    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.

Share This Page