How to insert data in encrypted varbinary field from flat file using SSIS?

Question

I have a table called demo with three fields.

ID int identity,
Name varbinary(128),
orderdate datetime

Demo table has the field 'Name' which is encrypted.

Now I have a flat file which has data like

1,John,2016-01-01

How would I go about achieving this in SSIS?

In TSQL, I can enter data like this

OPEN SYMMETRIC KEY STORE_CRYPT

DECRYPTION BY ASYMMETRIC KEY STORE_CRYPT_ASYM

INSERT INTO dbo.Demo (Name,OrderDate)
SELECT ENCRYPTBYKEY(KEY_GUID('STORE_CRYPT'),'John'),GETDATE()

CLOSE SYMMETRIC KEY STORE_CRYPT

Answer 1

Simplest and fastest way is to use a staging table where data is stored as varchar, then add an execute SQL Task that perform the encryption and insert data into the destination table.

Staging table structure

ID int identity,
Name varchar(255),
orderdate datetime

Execute SQL Task Command

OPEN SYMMETRIC KEY STORE_CRYPT

DECRYPTION BY ASYMMETRIC KEY STORE_CRYPT_ASYM

INSERT INTO dbo.Demo (Name,OrderDate)
SELECT ENCRYPTBYKEY(KEY_GUID('STORE_CRYPT'),Name),OrderDate 
FROM StagingTable

CLOSE SYMMETRIC KEY STORE_CRYPT

---

Other possible solution (not sure if it works)

Try using an OLEDB Command transformation instead of OLEDB Destination and use the following command: (Note that from performance perspective it is not recommended to use OLEDB command)

OPEN SYMMETRIC KEY STORE_CRYPT

DECRYPTION BY ASYMMETRIC KEY STORE_CRYPT_ASYM

INSERT INTO dbo.Demo (Name,OrderDate)
SELECT ENCRYPTBYKEY(KEY_GUID('STORE_CRYPT'),?),?

CLOSE SYMMETRIC KEY STORE_CRYPT

And map the Name column to the first parameter and Orderdate to the second