Ed Wagner (7/16/2014)
The encryption part stands alone. Once you have it working and understand it, then add in the other parts you need in your solution. This is the "divide and conquer" approach of problem-solving.Encrypted data works like any other data, so you can do your update and be done with it. You can still manipulate your table like any other table, but you have to allow for the encryption if you copy it across different servers. That's the part about knowing the big picture.
What's next? If you want to do something in SSIS, I'm going to bow out quickly. I can't do much beyond spelling it, and I've gotten that wrong a couple of times. 😛
But the one thing to remember is not to encrypt in place: if you have teh unencrypted SSNs, the first step is to add a column for the encrypted SSNs. Next put the encrypted SSNs in the new column without changing the old column, then check that everything works using the encrypted column, and when it does all work (and not before) delete the old column.
Tom