SSIS:“插入只读列 <ColumnName> 失败" [英] SSIS: "Failure inserting into the read-only column <ColumnName>"
问题描述
我有一个进入 OLE DB 目标的 Excel 源.我将数据插入到一个视图中，该视图有一个处理所有插入的 INSTEAD OF 触发器.当我尝试执行包时，我收到此错误:
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:
插入只读列 ColumnName 失败"
"Failure inserting into the read-only column ColumnName"
我该怎么做才能让 SSIS 知道此视图可以安全插入，因为有一个 INSTEAD OF 触发器可以处理插入?
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?
编辑(附加信息):
一些额外的信息.我有一个正被插入到规范化数据库中的平面文件.我最初的问题是如何获取一个平面文件并将该数据插入多个表中，同时跟踪所有主/外键关系.我的解决方案是创建一个模仿平面文件结构的 VIEW，然后在该视图上创建一个 INSTEAD OF 触发器.在我的 INSTEAD OF 触发器中，我将处理维护表之间所有关系的逻辑
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
...
我永远不需要从这个视图中选择，我只用它从我收到的这个平面文件中插入数据.我需要以某种方式告诉 SQL Server，这些字段并不是真正只读的，因为此视图上有一个 INSTEAD OF 触发器.
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.
推荐答案
这不是理想的解决方案，但我找到了解决问题的方法.由于 SSIS 抱怨插入到我的视图中，我创建了一个与我的视图具有完全相同结构的表.然后，在该表上的 INSTEAD OF 触发器中，我只是将发往该表的信息插入到视图中.这在导入过程中又增加了一个步骤，但没什么大不了的.
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.
这篇关于SSIS:“插入只读列 <ColumnName> 失败"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
