4

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Lee White
  • 3,649
  • 8
  • 37
  • 62
  • 1
    I've never had a problem with SSIS and it trying to insert values into a computed column. You would be better off, therefore, showing us the details of your Destination node. I also *assume* that the destination is not a `VIEW` or that the table doesn't have an triggers on it? – Thom A Jan 02 '20 at 14:41
  • I've had this issue now with 4 different databases that use the same data model. However, when I try to reproduce the issue by re-creating the table in my own sandbox, everything works fine. I'll test some more until I find some details that could be useful. – Lee White Jan 02 '20 at 15:41
  • 1
    Certainly sounds environment specific. The fact that you can't replicate the error when you try suggests something *else*; either the settings in the Destination are wrong, it's a `VIEW` without a `INSTEAD OF` trigger when it's required, or it's a table with a trigger that causes the error. We can't help you if we can't replicate the problem; and we have no chance with only the error, if you can't with all the details. – Thom A Jan 02 '20 at 15:47
  • Don't worry, I understand that. When I posted the question I was hoping that it was a slightly common problem that somebody would have an answer ready for. – Lee White Jan 02 '20 at 15:49
  • 1
    If it were that common, you'd have likely found a duplicate question when you researched the problem. :) – Thom A Jan 02 '20 at 15:51
  • Can you exclude it from the source query? It does sound like a metadata issue somewhere. I assume you can do the insert without the data flow? Like, just as a script or query. – Jacob H Jan 02 '20 at 17:08

2 Answers2

0

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

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)

Thomas Franz
  • 299
  • 1
  • 8