SSIS: "Failure inserting into the read-only column <ColumnName>"
- by Cory
I have an Excel source going into an OLE DB destination. I'm inserting data into a view that has an INSTEAD OF trigger that handles all inserts. When I try to execute the package I receive this error:
"Failure inserting into the read-only column ColumnName"
What can I do to let SSIS know that this view is safe to insert into because there is an INSTEAD OF trigger that will handle the insert?
EDIT (Additional info):
Some more additional info. I have a flat file that is being inserted into a normalized database. My initial problem was how do I take a flat file and insert that data into multiple tables while keeping track of all the primary/foreign key relationships. My solution was to create a VIEW that mimicked the structure of the flat file and then create an INSTEAD OF trigger on that view. In my INSTEAD OF trigger I would handle the logic of maintaining all the relationships between tables
My view looks something like this.
CREATE VIEW ImportView
AS
SELECT
CONVERT(varchar(100, NULL) AS CustomerName,
CONVERT(varchar(100), NULL) AS Address1,
CONVERT(varchar(100), NULL) AS Address2,
CONVERT(varchar(100), NULL) AS City,
CONVERT(char(2), NULL) AS State,
CONVERT(varchar(250), NULL) AS ItemOrdered,
CONVERT(int, NULL) AS QuantityOrdered
...
I will never need to select from this view, I only use it to insert data into it from this flat file I receive. I need someway to tell SQL Server that the fields aren't really read only because there is an INSTEAD OF trigger on this view.
