Cannot insert into read only column but actually it is not

SSIS and Import/Export
1

Hi,

I am trying to load fact table which is having 30 columns. There is a column 'Version_Number(timestamp)', when i mapped this in OLEDB Destination i am getting the error as "Cannot insert into read only column 'Version_number'" but actually it is not readable col(like Identity) in fact and data for this col is coming from source.
Also, i am not able to set this for update in OLEDB Command here i am getting the error as ' cannot do update for timestamp col'

I need to load this col but i couldn't found sol for this. Can any one help me on this?

2

Please post the DDL for the target table

3

It is through OLEDB DESTINATION there is no DDL for Insert.

For Update :
Update FactTable
Version_number = ? (col mapping from source cols)
where Care_Sk = ?(col mapping from source/lkp cols)

4

No there is no DDL for insert, but without the DDL for the tables (CREATE TABLE commands) your question may not be answerable.

5

My SSIS design looks like this,,,

OLEDB SOURCE ---> Select query on Source
then.....
LOOK UP---> Business Case
For Unmatched output... OLEDB Destination ( Mapping b/w src and dst)
For Matched output ...... OLEDB Command ( Update stmt to update existing cols)

Here, except in OLEDB Source and update stmt in OLEDB Command we are not writing any DDL stmt.

6

Still need the CREATE TABLE statements.

7

A timestamp/rowversion column just indicates when any of the other columns were last changed. It cannot be explicitly inserted. Just exclude the column from the insert list and it will sort itself out.

8

Hi,

I am understanding completely what you have said, but understood that i have to ignore this col from insertion and update.

If i do so, how date will be populated for this col.

9

Here is my table definition.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[RSN_Sen_Fact_20160203](
[reservation_id] [int] NOT NULL,
[time_of_care_id] [int] NOT NULL,
[number] [int] NOT NULL,
[date_from] [datetime] NOT NULL,
[date_to] [datetime] NOT NULL,
[service_cost] [decimal](18, 2) NULL,
[client_service_cost] [decimal](18, 2) NULL,
[usage_tx_status_type_id] [int] NULL,
[calculated_usage_id] [int] NOT NULL,
[description] varchar NULL,
[notes] nvarchar NULL,
[ax_invoice_id] nvarchar NULL,
[ax_sales_order_id] nvarchar NULL,
[reservation_manual_charge_create_user] nvarchar NULL,
[reservation_manual_charge_create_date] [datetime] NULL,
[reservation_manual_charge_last_update_date] [datetime] NULL,
[reservation_manual_charege_last_update_user] nvarchar NULL,
[reservation_manual_charge_status] [bit] NULL,

[version_number] [timestamp] NULL,

[payment_tx_id] [nvarchar](50) NULL,
[payment_date] [datetime] NULL,
[payment_recorded] [bit] NULL,
[process_auto_payment] [bit] NULL,
[reservation_care_session_sk] [int] NOT NULL,
[reservation_sk] [int] NULL,
[benefit_holder_person_sk] [int] NULL,
[benefit_recipient_person_sk] [int] NULL,
[usage_rule_sk] [int] NULL,
[care_recipient_sk] [int] NULL,
[provider_sk] [int] NULL,
[employee_bank_sk] [int] NULL,
[client_bank_sk] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

10

Timestamp/Rowversion is not a date. It is used by applications to check if a row has changed.

https://msdn.microsoft.com/en-us/library/ms182776.aspx

1 Like