Identity insert to SSIS ADO.NET Always Encrypted destination

Question

I am trying to copy a table in plain text to another one with same number of columns but certain columns are encrypted using SQL Server Always Encrypted technology.

In a nutshell this is what I am doing:

On the connection manager, I have set retainsameconnection=true and MultipleActiveResultset=true. ColmnEncryptionsetting is enabled. On the ADO.NET destination I have set UseBulkInsertWhenPossible=false.

When I run the pacakge with this setting, I am getting encryption column error:

[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: Operand type clash: nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'QC_AE_CEK_1', column_encryption_key_database_name = 'MyDBNAME') is incompatible with nvarchar(1000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'QC_AE_CEK_1', column_encryption_key_database_name = 'MyDBNAME')  
Statement(s) could not be prepared.  

If I disable usebulkisertwhenpossible, and keep retainsameconnection=true I get this error:

[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: Explicit value must be specified for identity column in table 'MyTableName' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.  

If I set both retainsameconnection and usebulkinsert to false, the insert goes through, but I can't insert identity column here. It simply ignores Identity Insert ON and inserts new values to the identity column.

How do I copy a table that is not encrypted to an always encrypted schema retaining the identity column values?

Answer 1

Hello All,
We have got a solution for the scenario identified.
My source query replaces '' with NULL like I did in the previuos comment-

select  id,name,
case ADDRESS when '' then NULL else ADDRESS end as ADDRESS
from source_flat with (nolock)

I added a derived column between my source and destination and added this code to it.

REPLACENULL(ADDRESS," ")

Used the new derived column for the insert into the destination.
This worked with identity insert for me.
Tested it for both date columns and nvarchar fields and it works for both scenarios.

Answer 2

Hi @Roopesh Parambath ,

1.nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'QC_AE_CEK_1', column_encryption_key_database_name = 'MyDBNAME') is incompatible with nvarchar(1000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'QC_AE_CEK_1', column_encryption_key_database_name = 'MyDBNAME')

Could you please share the whole example of sql statements and change the datatype as same?

2.Explicit value must be specified for identity column in table 'MyTableName' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Could you please check if you set the identity column correctly in table 'MyTableName'?

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi Mona,
Apologies for the late response. I had to multiple tests to identify the exact scenario giving this error.

I checked all the columns in my table and did confirm that there are no columns with nvarchar(4000). Not sure where is it coming from.
To test it further to know if this is an issue with nvarchar columns only, I created a test table and run my package against that. I am able to reproduce the issue and identify the scenario of this error now. However, still clueless on why is it behaving so and what is the fix.

Here is the schema of my tables.

CREATE TABLE [dbo].[source_flat](  
 [id] [bigint] IDENTITY(1,1) NOT NULL,  
 [name] [nvarchar](1000) NULL,  
 [address] [nvarchar](256) NULL,  
PRIMARY KEY CLUSTERED   
(  
 [id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

Destination table:

CREATE TABLE [dbo].[destn_flat](  
 [id] [bigint] IDENTITY(1,1) NOT NULL,  
 [name] [nvarchar](1000) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [QC_AE_CEK_1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,  
 [address] [nvarchar](256) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [QC_AE_CEK_1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,  
PRIMARY KEY CLUSTERED   
(  
 [id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

Note that the destination is encrypted.

Data in source:

Note that the first row has an empty string for the address and this is what gives the error.
If I update the address column of the first row to a string value, the package goes through fine.

I also tried these queries in the source selection, but I am still getting the same error.

Here is the error:  
[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: Operand type clash: **nvarchar(4000)** encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'QC_AE_CEK_1', column_encryption_key_database_name = 'MyDB') is incompatible with **nvarchar(256)** encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'QC_AE_CEK_1', column_encryption_key_database_name = 'MyDB')  
Statement(s) could not be prepared.  

Now we know the scenario:
When loading data with identity insert ON to an Always Encrypted destination table using ADO.NET connection, and if the source column has an empty string for an encrypted column, then SSIS throws the error nvarchar(4000) does not match with nvarchar(column size in destination table). Why is it behaving so and what is the fix for this?

Answer 4

Thanks for everyone sharing problem solution.