如何使用 SSIS 包将 MS Access 数据导入 SQL Server？

Question

我花了一天的大部分时间四处寻找任何可以帮助我的东西，但是 SSIS 是一个如此庞大的工具，到目前为止没有任何用处，或者也许我只是不理解它。

我需要将一个有问题的旧 Access 数据库移至 SQL Server 中。我已经在 SQL 数据库中设置了架构。旧数据需要修复，例如标准化和删除重复项。我的老板坚持使用 SSIS 来完成这项工作，因为我们需要有人知道如何使用它，而目前几乎没有人知道（一位经理很久以前就使用过它）。

所以，我在 BDIS、视觉工作室或任何这个应用程序中设置了一个项目。我创建了一个到 Access 数据库的连接管理器，并设法将 OLE DB 源拖到设计器上并将其设置为连接到该数据库。我还发现我可以从那里选择数据。

现在怎么办？我真的更喜欢有一种方法，只编写一个巨大的 SQL 脚本来从 Access 数据库中获取我需要的所有数据，按照我想要的方式转换它，然后将其推送到 SQL Server 数据库中。但看起来我需要在设计器中使用所有这些时髦的转换工具。我也不知道如何将结果数据输入 SQL Server。我在服务器资源管理器窗格中设置了连接，但网上的每个人都说永远不要使用 SQL Server 目标。所以我也在那里迷路了。

我的老板说联机丛书帮助文件非常有用。到目前为止，这就像在沼泽下大海捞针一样，上面坐着三座城堡。信息太多了，但对我来说似乎没有什么用处。

编辑

希望更多信息会有用。我认为 SSIS 附带的向导对于我想要的东西来说不够强大，所以如果它们足够强大，你就必须解释一下。这是我必须做的一个例子，但我的现实是有更多的表和更多的转换。

假设我有一个如下所示的源表：

Companies
====================================================
| Name    | Address    | WidgetOne   | WidgetTwo   |
|--------------------------------------------------|
| ACME    | 123 etc.   | Trampoline  | Cannon      |
====================================================

我需要将其规范化为两个表。显然，它必须跟踪 ID，以便小部件与正确的公司相关联。如果您可以提供一个示例来说明如何使用 SSIS（从 Access 到 SQL Server）处理这种情况，那么我可能可以从那里获取它。谢谢！

Answer 1

以下示例可能会给您提供从 MS Access 到 SQL Server 的数据迁移的想法。该示例使用 MS Access 2010 和 SQL Server 2008 R2 数据库。该包是用 SSIS 2008 R2 编写的。与之前提供的解决方案不同，此答案不使用 SQL Server 导入和导出向导，并且该包是从头开始构建的。

分步过程：

1. 假设 Access 表如屏幕截图 #1 所示，其中包含一个名为 Companies 的表，其中包含以非规范化方式包含两行。

2. 还假设 SQL Server 中的表结构如屏幕截图 #2 和 #3 所示，其中有两个名为 dbo.CompanyInfo 和 dbo.WidgetInfo。 SQL 脚本部分下提供了创建表脚本。表是空的，如屏幕截图 #4 所示。

3. 创建一个新的 SSIS 包。在 SSIS 包上，右键单击连接管理器并选择“新建 OLE DB 连接”，如屏幕截图 #5 所示。在“配置 OLE DB 连接管理器”上，单击“新建...”按钮，如屏幕截图 #6 所示。

4. 在连接管理器上，选择本机 OLE DB/Microsoft Jet 4.0 OLE DB 提供程序 并提供 Access 文件路径。在此示例中，我的文件位于 C:\temp\Source.mdb 中。单击“确定”。请参阅屏幕截图#7。在“配置 OLE DB 连接管理器”上，单击“确定”，如屏幕截图 #8 所示。将连接管理器名称更改为 AccessDB（该名称可以是您偏好的任何名称）。请参阅屏幕截图 #9。

5. 再次右键单击“连接管理器”并选择“新建 OLE DB 连接”，如屏幕截图 #10 所示。这次我们将为 SQL Server 创建一个连接字符串。选择 Native OLE DB\SQL Server Native Client 10.0 并提供服务器名称和数据库名称，如屏幕截图 #11 所示。将连接管理器重命名为 SQLServer（同样，名称是您的选择）。请参阅屏幕截图 #12。

6. 在 SSIS 包的控制流选项卡上，放置一个数据流任务并将其命名为 Populate CompanyInfo。双击数据流任务，切换到“数据流”选项卡。在数据流任务中，放置一个 OLE DB Source、一个 Derived Transformation 和一个 OLE DB Destination，如屏幕截图 #13 所示。 注意：您需要按照显示的顺序一项一项地配置任务。不要同时放置所有任务并尝试连接它们。

7. 配置 OLE DB 源以读取 Access 数据库表，如屏幕截图 #14 和 #15 所示。配置派生转换以将字符串文本从 Access 数据库转换为 Unicode，如屏幕截图 #16 所示。配置 OLE DB 目标以将数据插入 SQL 表，如屏幕截图 #17 和 #18 所示。

8. 返回“控制流”选项卡并放置另一个数据流任务，如屏幕截图 #19 所示。

9. 在第二个数据流任务中，我们读取 Access 数据库中的同一个 Companies 表，并尝试在 SQL 中填充 WidgetInfo 表。

10. 放置一个 OLE DB Source 来读取 Access 表并按照屏幕截图 #20 和 #21 中所示进行配置。放置派生转换以将字符串转换为 Unicode，如屏幕截图 #22 所示。放置查找转换以根据名称和地址获取 CompanyId 并配置任务，如屏幕截图 #23 和 #24 所示。如果找不到匹配项，默认情况下查找任务将会失败。我们需要标准化 Widget 数据。因此，放置一个Unpivot 转换并按屏幕截图#25 所示进行配置。放置一个 OLE DB 目标，将数据插入到 SQL 中并按屏幕截图 #26 和 #27 所示进行配置。第二个数据流任务如屏幕截图所示 #28

11. 屏幕截图 #29 - #31 显示示例包执行情况。

12. 屏幕截图 #32 显示包执行后 SQL 表中的数据。

我希望这能够提供将数据从 Access 数据库导出到 SQL Server 的想法。您可以在 Access 中对表进行分组，并将它们加载到单个数据流任务中。如果存在依赖于其他表的表，则您可以将其放置在单独的数据流任务中，如此示例中所示。

希望有帮助。

SQL 脚本：

CREATE TABLE [dbo].[CompanyInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](80) NOT NULL,
    [CompanyAddress] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_CompanyInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WidgetInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyId] [int] NOT NULL,
    [WidgetName] [nvarchar](40) NOT NULL,
CONSTRAINT [PK_WidgetInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

屏幕截图 #1：

屏幕截图#2:

屏幕截图 #3:

屏幕截图 #4：

屏幕截图 #5：

屏幕截图 # 6：

屏幕截图 #7：

屏幕截图 #8：

屏幕截图#9：

屏幕截图 #10：

屏幕截图 #11：

截屏#12:

屏幕截图 #13:

屏幕截图 #14:

屏幕截图 #15：

屏幕截图 #16：

屏幕截图 #17：

屏幕截图 #18：

屏幕截图 #19：

屏幕截图#20:

屏幕截图 #21:

屏幕截图 #22:

屏幕截图 #23：

屏幕截图 #24：< /strong>

屏幕截图#25：

屏幕截图 #26：

屏幕截图 #27：

屏幕截图#28:

屏幕截图 #29:

屏幕截图 #30:

屏幕截图 #31：

屏幕截图 #32：

Answer 2

这可能会有所帮助：

1. 您将需要连接到源数据库和目标数据库。听起来您已经有了源。

2. 您需要在“控制流”选项卡中执行数据流任务。拖动其中一个并双击 - 您将进入“数据流”选项卡。

3. 这里添加一个“数据源”（听起来您已经完成了）并添加一个“OLE DB 目标”。

4. 双击您的来源。您应该能够指定连接（再次看起来您已经这样做了），

5. “数据访问模式”指定您想要如何检索数据。即直接从表中获取数据，或编写返回数据的查询

6. 如果它是到目标的直接一对一映射，您应该能够使用绿线（优先约束）将两者连接起来。< /p>

如果您想要执行任何转换，则可以在源查询中执行此操作，或者将转换对象之一放在源和目标之间并相应地连接它们。