decrypt data in column before load into destination

Question

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.

regards,

Answer 1

When calls to decrypt methods return NULL, either the column is NULL or the account that is using the methods doesn't have access to the key.

From BOL

Permissions

Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.

Look at at the account that is executing the SSIS package and verify it has the required permissions.

You can also launch SSMS as the account that runs the SSIS packages and try stepping through the underlying code of DecryptByKeyAutoCert. Something approximately like

OPEN SYMMETRIC KEY MyKeyNameHere
   DECRYPTION BY CERTIFICATE Usercert;

SELECT 
    city_id
,   CONVERT(nvarchar(50), DecryptByKey(city_name)) AS city_name
FROM city;

CLOSE SYMMETRIC KEY MyKeyNameHere;