I have a table named city which contains two columns
- city_id (int)
- city_name (varbinary) -- encrypted column
I want to extract data from this table, transform (decrypt the city_name) and load (decrypted city_name) into new table (destination).
I used the following query in oledb source in ssis. but it returns city_name column as NULL.
select city_id, CONVERT(nvarchar(50), decryptbykeyautocert(cert_id('Usercert'),NULL,city_name)) as city_name from city
The above query works fine in sql server management studio query editor.
plz guide how to decrypt data before insert into destination.