Expert SQL Server™ 2005 Integration Services

2.5. Encrypting Data Through Script Components

So far, most of this chapter has focused on the Script Task, which is in the control flow of your package. In the data flow, you use a Script Component, which morphs into a Script Source, Transform, or Destination. The code that's stubbed out for each of the components varies based on what role you want the Script Component to play. More information on this can be seen in the Professional SQL Server 2005 Integration Services book. This section shows you how to use the power of the data flow to transform data into an encrypted form.

As the world of technology matures, the unfortunate reality is that new methods of hacking mature as well. In earlier versions of SQL Server, there was nothing that handled encryption. Within SQL Server 2005, however, there is built-in functionality that handles encryption and decryption of data. Obviously, certain types of data should not be left in plain text within a database (data such as credit card numbers and Social Security numbers, for example). Wouldn't it be nice to have a component that handles encryption as well?

SSIS does not have a component that handles encryption and decryption out-of-the-box. However, the Script Component can be extended to use a custom library that encapsulates the encryption/decryption for you. The component can then be reused within other packages.

When using encryption, you should understand how it works, as well as what types of encryption are available. There are ...