SSIS - 数据流任务未正确插入自动增量字段 [英] SSIS - Auto increment field is not inserted correctly with data flow task

问题描述

我正在尝试使用 ssis 将数据从一个数据库复制到另一个数据库.我使用 SQL Server 导入和导出向导创建了 dtsx 包.我从中复制的表有一个列名Id"，另一个表的名称是ModuleCategoryId"，我将它们映射在一起.

I am trying to copy data from one database to another using ssis. I created the dtsx package with the SQL Server Import and Export Wizard. The table I am copying from has a column name "Id", the other table has name "ModuleCategoryId", which I mapped together.

ModuleCategoryId 是身份，自动递增 1.在源数据库中，Id 没有排序，如下所示:

ModuleCategoryId is the identity, and has an auto increment of 1. In the source database, the Id's are not ordered, and go like this:

• 32 Name1
• 14 Name2
• 7 Name3

执行数据流后，目标DB如下所示:

After executing the data flow, the destination DB looks like this:

• 1 名 1
• 2 Name2
• 3 Name3

我在向导中启用了身份插入，但这没有任何作用.

I have enabled identity insert in the wizard during the, but this doesn't do anything.

目标数据库是用实体框架制作的，代码优先.

The destination database was made with Entity Framework, code first.

如果我明确关闭 ValueGeneratedOnAdd，并重新制作目标数据库，数据将被正确传输，但我想知道是否有一种方法可以在不关闭自动增量的情况下传输所有数据，然后重新打开它.

If I explicitly turn off ValueGeneratedOnAdd, and remake the destination database, the data is being transferred correctly, but I was wondering if there's a way to transfer all the data without turning off the auto increment, and then turning it back on.

如果我手动为该表设置了 Identity Insert，我可以插入带有我想要的任何 ModuleCategoryId 的行，因此它必须与数据流有关.

If I manually set Identity Insert on for that table, I can insert rows with whatever ModuleCategoryId I want, so it must be something with the dataflow.

推荐答案

表定义是表定义 - 无论语法糖 ORM 工具可能覆盖.

Table definitions are table definitions - regardless of the syntactic sugar ORM tools might overlay.

我创建了一个源表和目标表，并填充了源以匹配您提供的数据.我也在目标表上定义了标识属性.这是否是在 API 中实现的 ValueGeneratedOnAdd，我不知道，但它几乎必须是否则启用身份插入应该失败(如果 UI 甚至允许它).

I created a source and destination table and populated the source to match your supplied data. I do define the identity property on the destination table as well. Whether that's what a ValueGeneratedOnAdd is implemented as in the API, I don't know but it almost has to be otherwise the Enable Identity Insert should fail (if the UI even allows it).

IDENTITY 属性允许您使用您想要的任何初始值为其设定种子.对于目标表，我以有符号整数允许的最小值作为种子，这样如果身份插入不起作用，结果值看起来真的错误"

The IDENTITY property allows you to seed it with any initial value you want. For the taget table, I seed at the minimum value allowed for a signed integer so that if the identity insert doesn't work, the resulting values will look really "wrong"

DROP TABLE IF EXISTS dbo.SO_67370325_Source;
DROP TABLE IF EXISTS dbo.SO_67370325_Destination;

CREATE TABLE dbo.SO_67370325_Source
(
    Id int IDENTITY(1,1) NOT NULL
,   Name varchar(50)
);
CREATE TABLE dbo.SO_67370325_Destination
(
    ModuleCategoryId int IDENTITY(-2147483648,1) NOT NULL
,   Name varchar(50)
);

CREATE TABLE dbo.SO_67370325_Destination_noident
(
    ModuleCategoryId int NOT NULL
,   Name varchar(50)
);

SET IDENTITY_INSERT dbo.SO_67370325_Source ON;
INSERT INTO DBO.SO_67370325_Source
(
    Id
,   Name
)
VALUES
    (32, 'Name1')
,   (14, 'Name2')
,   (7, 'Name3');
SET IDENTITY_INSERT dbo.SO_67370325_Source OFF;

INSERT INTO dbo.SO_67370325_Source
(
    Name
)
OUTPUT Inserted.*
VALUES
(
    'Inserted naturally' -- Name - varchar(50)
);

除了您提供的 3 个值之外，我添加了第四个，如果您运行所提供的查询，您将看到生成的 ID 可能是 33.源表创建时的标识为 1，但显式标识插入源表表将种子值提前到 32.假设没有其他活动发生，下一个值将是 33，因为我们的增量是 1.

Beyond your 3 supplied values, I added a fourth and if you run the supplied query, you'll see the generated ID is likely 33. Source table is created with an identity seeded at 1 but the explicit identity inserts on the source table advance the seed value to 32. Assuming no other activity occurs, next value would be 33 since our increment is 1.

说了这么多，我已经建立了 3 个场景.在导入导出向导中，我检查了 Identity Insert 并将 Id 映射到 ModuleCategoryId 并运行了包.

All that said, I have 3 scenarios established. In the Import Export wizard, I checked the Identity Insert and mapped Id to ModuleCategoryId and ran the package.

ModuleCategoryId|Name
32|Name1
14|Name2
7|Name3
33|Inserted naturally

目标表中的数据与源表中的数据相同 - 正如预期的那样.此时，身份种子位于 33，我可以使用一些我不方便的 DBCC 检查命令进行验证.

The data in the target table is identical to the source - as expected. At this point, the identity seed is sitting at 33 which I could verify with some DBCC check command I don't have handy.

下一个案例是采用相同的包并取消选中 Identity Insert 属性.这将变得无效，因为我会收到错误报告

The next case is taking the same package and unchecking the Identity Insert property. This becomes invalid as I'd get an error reporting

插入只读列ModuleCategoryId"失败

Failure inserting into the read-only column "ModuleCategoryId"

唯一的选择是将 Id 取消映射到 ModuleCategoryId.假设我像以前一样加载到同一张表，我会看到类似这样的数据

The only option is to unmap the Id to ModuleCategoryId. Assuming I loaded to the same table as before, I would see data something like this

ModuleCategoryId|Name
34|Name1
35|Name2
36|Name3
37|Inserted naturally

如果我从来没有将记录放入这个表中，那么我会得到这样的结果

If I had never put a record into this table, then I'd get results like

ModuleCategoryId|Name
-2147483648|Name1
-2147483647|Name2
-2147483646|Name3
-2147483645|Inserted naturally

如果没有我的来源明确订购，则无法保证订购结果.我经常打这场仗.除非您明确要求，否则 SQL 引擎没有义务按主键顺序或任何其他此类顺序返回数据.如果存储了以下结果，则同样有效.

WITHOUT AN EXPLICIT ORDER BY ON MY SOURCE, THERE IS NO GUARANTEE OF RESULTS ORDERING. I fight this battle often. The SQL Engine has no obligation to return data in the primary key order or any other such order unless you explicitly ask for it. Had the following results been stored, it would be equally valid.

ModuleCategoryId|Name
34|Inserted naturally
35|Name1
36|Name2
37|Name3

如果您有数据需要根据源表中Id的升序值插入到目标表中，在导入/导出向导中，您需要转到询问您是否要插入的屏幕选择表或编写查询并选择查询的第二个选项.然后，您将编写 SELECT * FROM dbo.SO_67370325_Source ORDER BY Id; 或任何源表的名称.

If you have a requirement for data to be inserted into the target table based on the ascending values of Id in the source table, in the Import/Export wizard, you need to go to the screen where it asks whether you want to pick tables or write a query and choose the second option of query. Then you will write SELECT * FROM dbo.SO_67370325_Source ORDER BY Id; or whatever your source table is named.

加载 SO_67370325_Destination_noident 的最终测试演示了一个没有定义身份属性的表.如果我不将 Id 映射到 ModuleCategoryId，则该包将失败，因为该列被定义为 NOT NULL.当我将 Id 映射到 ModuleCategoryId 时，我将看到与第一个 (7,14,32,33) 相同的结果，但是，对目标表的每个后续插入都必须提供自己的 Id，该 Id 可能与也可能不一致你的 FluentAPI 东西可以.

The final test, loading SO_67370325_Destination_noident, demonstrates a table with no identity property defined. If I do not map Id to ModuleCategoryId, the package will fail as the column is defined as NOT NULL. When I map the Id to ModuleCategoryId, I will see the same results as the first (7,14,32,33) BUT, every subsequent insert to the target table will have to provide their own Id which may or may not align with what your FluentAPI stuff does.

类似的问题/答案 错误 0xc0202049:数据流任务 1:插入只读列失败

这篇关于SSIS - 数据流任务未正确插入自动增量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！