使用SSIS迁移和规范化数据库

Question

我们有一个 MS Access 数据库，希望将其迁移到具有新数据库设计的 SQL Server 数据库。 使用 SQL Server DB 的应用程序的一部分已经编写完成。

我四处寻找如何最轻松地完成迁移步骤，并从 Microsoft 的 SQL Server Integration Services (SSIS) 开始。 现在，出于标准化原因，我想垂直拆分表格。

一个虚构的示例如下所示

MS Access 表 person

ID
Name
Street

SQL Server 表 person

id
name

SQL Server 表 address

id
person_id
street

如何使用 SSIS 最好地完成此任务？ id 列是标识（自动增量）列，因此我无法插入旧 ID。 如何将正确的 person_id 外键放入地址表中？

甚至可能有一个表必须分成三个表，其中 table2 中的一行属于 table1，table3 中的一行属于 table2 的行。

SSIS 是实现此目的的适当方法吗？

编辑 虽然这是一次性迁移，但我们需要一个自动化且可重复的过程，因为生产数据库使用率很高，并且我们正在开发环境中使用最近但不是最新的数据进行迁移。 我们计划进行一次迁移测试，并让客户审查该行为。 如果一切顺利，我们将进行真正的迁移。

大多数给定的解决方案都包含大量手动步骤，因此并不合适。

Answer 1

使用执行 SQL 任务并自己编写语句。

对于父表，请执行Select into table from table...，然后在进行过程中对其余表执行相同的操作。 确保将父表的标识插入设置为 ON 并重复使用旧的 ID。 这将帮助您保持数据完整性。

Answer 2

要将 Access 表迁移到 SQL Server，请使用 SSMA，不是 Access 的升迁向导。
您将获得更多可用的工具。

然后，您可以在 SQL Server 中一张一张地分解表。
我不确定是否有任何工具可以帮助您自动拆分表格，至少我找不到任何工具，但是手动完成并不太困难，尽管需要多少工作取决于您如何使用原始表格首先在您的 VBA 代码和表单中。

附注

关于标准化，不要太过分：我知道您的示例就是这样，但并不总是（很少？）需要标准化客户地址。

一个人可以有多少个地址？
如果您算上家庭地址、营业地址、送货地址、帐单地址，这可能是您需要的最多的地址。
在这种情况下，最好将它们放在同一个表中。 标准化数据只会需要更多的工作来重新组合，并且没有任何好处。
当然，在某些情况下，标准化是有意义的，但我看到人们过度接受这个概念（我也对此感到内疚），然后发现自己正在努力构建更复杂的查询来加入所有这些分裂数据，使开发和维护变得更加困难，并且经常在此过程中遭受性能损失。

Answer 3

Access 是如此用户友好，为什么不在 Access 中标准化您的表，然后从那里扩大最终的结构呢？

Answer 4

我找到了一个尚未提及的不同解决方案，它允许我们使用数据流任务的所有便利性和选项：

如果目标数据库位于本地 SQL Server 上，则可以使用数据流任务SQL Server 目标而不是 OLE DB 目标。
对于 SQL Server 目标，您可以标记“保留身份”选项。 （我不知道英文名称是否正确，因为我们有德语版本。）有了这个，你可以写入标识列

我们发现我们不能在任何地方使用旧的主键，因为我们有一些表采用并集来自多个表的记录。

我们通过构建一个包含列的临时映射表来开始该过程

new_id (identity)
old_id (int)
old_tablename (string)

。我们首先为新模式中的外键引用的每个表填充所有 old_id 。 new_id 值由 SQL Server 自动生成。

因此，我们可以在需要时使用连接将 old_id 转换为 new_id。 我们使用 new_id 值通过“保留身份”选项填充新表中的身份（主键）列，并且可以通过联接在映射表中简单地查找它们的外键。

Answer 5

您还可以查看 Jamie Thomson 的 SSIS 标准化器 组件。 我今天才发现（还没有实际尝试过）。 他发布的示例看起来很像您问题中的示例。