SSIS: "Failure inserting into the read-only column <ColumnName>"

Question

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.

Answer 1

Why is the column "read only"? Could you post schema for the view and the underlying table(s)? Is the column IDENTITY? Is there a WITH CHECK OPTION on the view? Is it a derived (calculated) column?

UPDATE:

I see now, a bit unusual application of a view, maybe a stored procedure would have been more appropriate choice -- a stored procedure in the DB and an OLEDB Command in SSIS.

Your final solution with a table as a destination is actually faster, providing that you do not use trigger, but use bulk-insert from the staging table to "final" tables.

Answer 2

It's not an ideal solution but I found a workaround to my problem. Since SSIS was complaining about inserting into my view I created a table with the exact same structure as my view. Then, in an INSTEAD OF trigger on that table, I merely insert the information destined for the table into the view. This adds one more step into the import process but is not a big deal.