SSIS fails to insert data into table that has computed column

Question

I have an SSIS flow to insert data into a table. This flow used to work fine, until the data model was updated and a computed column was added. Now, when I attempt to load data, I get the following error:

SQL Server Error Messages - Msg 271 - The column '' cannot be modified because it is either a computed column or is the result of a UNION operator.

I have found a good explanation for this error message here: SQL Server Error Messages - Msg 271

The reason why I am reaching out to StackOverflow, is because this column is, in fact, not mapped in my SSIS flow. This means that my OLEDB Destination component is aware of the column's existance, but its mapping is set to <ignore column>. This is the way I treat other columns such as generated IDs, and it always works fine.

So, hence the question: why am I getting this error message?

Once idea I have is that SSIS may be auto-generating SQL insert statements that map null values to this computed column. If this is the case, is there any way to force SSIS to completely ignore the column's existence?

Answer 1

I had the same problem after upgrading from SQL Server 2016 to 2019 with some target tables that contained computed columns (which were set to persisted).

Since the target tables were about 1 TB each a rebuild was no real option (or only the last resort). Creating another, identical table and using it as target did not help.

I bypassed the problem by creating a view (v_<table_name>_insert_target_from_ssis) that did nothing more than a stupid SELECT from the target table (without any joins, calculations, where conditions ...) and had all except the computed columns in the field list. After using this dummy view as destination in SSIS, the import job runs successfully again.

Another solution was to drop the PERSISTED from the computed column in the table (or in my case the empty copy, that I created for testing purposes). This can be done by an

ALTER TABLE dbo.big_dwh_table ALTER COLUMN I_am_computed DROP PERSISTED

After this change I was able to insert into this table with SSIS too (but it was no option for the original table because the computed column was very heavy used)

Answer 2

I think that the problem is that you are using fast load option when loading data into OLE DB Destination, try using regular table load since this issue may be caused during BULK INSERT operations.

Also make sure that the expression that generates the value for the computed column is not be getting evaluated as NULL in the inserted rows.