如何使用 SSIS 包将 MS Access 数据导入 SQL Server? [英] How do I import MS Access data into SQL Server using SSIS package?

查看：33
本文介绍了如何使用 SSIS 包将 MS Access 数据导入 SQL Server?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我花了一天的大部分时间四处寻找任何可以帮助我的东西，但是 SSIS 是一个如此巨大的工具，到目前为止没有任何用处，或者我可能只是没有用'不明白.

我需要将一个有问题的旧 Access 数据库移到 SQL 服务器中.我已经在我的 SQL 数据库中设置了架构.旧数据需要修复，例如规范化和删除重复项.我的老板坚持使用 SSIS 来完成这项工作，因为我们这里需要一个知道如何使用它的人，而目前几乎没有人会使用它(一位经理很久以前使用过).

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

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

我的老板说联机丛书的帮助文件非常有用.到目前为止，这就像在淹没在沼泽下的干草堆中找到一根针，上面有三座城堡.信息太多了，对我来说似乎没有任何用处.

编辑

希望更多信息有用.我认为 SSIS 附带的向导不够强大，无法满足我的要求，所以如果是，您必须解释一下.这是我必须做的一个例子，但我的现实是有更多的表格和更多的转换.

假设我有一个看起来像这样的源表:

公司====================================================|姓名 |地址 |WidgetOne |小工具二 ||--------------------------------------------------||ACME |123等|蹦床 |大炮 |====================================================

我需要将其标准化为两个表.它显然必须跟踪 ID，以便小部件与正确的公司相关联.如果您可以帮助举例说明如何使用 SSIS 处理这种情况，从 Access 到 SQL 服务器，那么我可能可以从那里获取.谢谢！

解决方案

以下示例可能会给您提供从 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. 在连接管理器上，选择 Native OLE DB/Microsoft Jet 4.0 OLE DB Provider 并提供 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 中的方式.如屏幕截图 #22 所示，放置一个 派生转换 以将字符串转换为 Unicode.放置一个 Lookup 转换 以根据名称和地址获取 CompanyId 并配置任务，如屏幕截图 #23 和 #24 中所示.默认情况下，如果找不到匹配项，查找任务将失败.我们需要对 Widget 数据进行规范化.因此，放置一个 Unpivot 转换 并按照屏幕截图 #25 所示进行配置.放置一个 OLE DB Destination 以将数据插入 SQL 并按屏幕截图 #26 和 #27 中所示进行配置.第二个数据流任务将如屏幕截图 #28

    11. 所示

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

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

我希望这应该能提供将数据从 Access 数据库导出到 SQL 服务器的想法.您可以在 Access 中对表进行分组并在单个数据流任务中加载它们.如果存在依赖于其他表的表，那么您可以按照本示例中的演示放置在单独的数据流任务中.

希望有所帮助.

SQL 脚本:

创建表 [dbo].[CompanyInfo]([Id] [int] IDENTITY(1,1) 非空，[公司名称] [nvarchar](80) 非空，[公司地址] [nvarchar](255) 非空，约束 [PK_CompanyInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]走创建表 [dbo].[WidgetInfo]([Id] [int] IDENTITY(1,1) 非空，[CompanyId] [int] 非空，[WidgetName] [nvarchar](40) 非空，约束 [PK_WidgetInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]走

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

屏幕截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12:

屏幕截图 #13:

屏幕截图 #14:

屏幕截图 #15:

屏幕截图 #16:

屏幕截图 #17:

屏幕截图 #18:

屏幕截图 #19:

屏幕截图 #20:

屏幕截图 #21:

屏幕截图 #22:

屏幕截图 #23:

屏幕截图 #24:

屏幕截图 #25:

屏幕截图 #26:

屏幕截图 #27:

屏幕截图 #28:

屏幕截图 #29:

屏幕截图 #30:

屏幕截图 #31:

屏幕截图 #32:

I've spend the better part of a day looking around for anything to help me, but SSIS is such a huge tool that nothing is of any use so far, or maybe I just don't understand it.

I need to take an old Access db that has some problems and move it into SQL server. I already have the schema set up in my SQL db. The old data needs to be fixed up, things like normalization and removing duplicates. My boss insists on using SSIS for this job, because we need someone here who knows how to use it, and currently almost no one does (one manager used it a long time ago).

So, I have a project set up in BDIS, or visual studio, or whatever this app actually is. I created a connection manager to my Access db, and I managed to drag an OLE DB Source onto the designer and set it up to connect to that db. I also figured out that I can select data from there.

Now what? I'd really prefer to have a way to just write a giant SQL script to grab all the data I need from the Access db, transform it how I want, and shove it into the SQL server database. But it looks like I'll need to use all these funky transformation tools in the designer. I also can't figure out how I'll get the resulting data into SQL server. I have the connection set up in the Server Explorer pane, but everyone online says never use the SQL Server Destination. So I'm lost there as well.

My boss said the Books Online help files would be very useful. So far, it's been like finding a needle in a haystack submerged under a swamp, with three castles sitting on top of it. There's just too much information, and none of it seems useful to me.

Edit

Hopefully some more information will be useful. I think the wizards that come with SSIS aren't powerful enough for what I want, so if they are, you'll have to please explain it. Here's an example of what I have to do, except my reality is a lot more tables with more transformations.

Say I have a source table that looks like this:

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

I need to normalize this into two tables. And it will obviously have to track the IDs so that the widgets are associated with the correct company. If you could help with an example of how this case would be handled with SSIS, from Access to SQL server, then I can probably take it from there. Thanks!

解决方案

Following example probably might give you an idea to perform data migration from MS Access to SQL Server. The example uses MS Access 2010 and SQL Server 2008 R2 database. The package was written in SSIS 2008 R2. Unlike the previously provided solution, This answer doesn't use the SQL Server Import and Export Wizard and the package was built from ground-up.

Step-by-step process:

  1. Let's assume that the Access table is as shown in screenshot #1 with a table named Companies containing two rows in a de-normalized fashion.

  2. And also assuming that the table structure in SQL Server is as shown in screenshots #2 and #3 with two tables named dbo.CompanyInfo and dbo.WidgetInfo. Create table scripts are provided under SQL Scripts section. The tables are empty as shown in screenshot #4.

  3. Create a new SSIS package. On the SSIS package, right-click on the connection manager and select New OLE DB Connection as shown in screenshot #5. On the Configure OLE DB Connection Manager, click New... button as shown in screenshot #6.

  4. On the Connection Manager, select Native OLE DB/Microsoft Jet 4.0 OLE DB Provider and provide the Access file path. In this example, I have the file in C:\temp\Source.mdb. Click OK. Refer screenshot #7. On the Configure OLE DB Connection Manager, click OK as shown in screenshot #8. Change the connection manager name to AccessDB (the name could be anything of your preference). Refer screenshot #9.

  5. Again, right-click on the Connection manager and select New OLE DB Connection as shown in screenshot #10. This time we are going to create a connection string for SQL Server. Select Native OLE DB\SQL Server Native Client 10.0 and provide the Server name and database name as shown in screenshot #11. Rename the connection manager to SQLServer (again, name is your choice). Refer screenshot #12.

  6. On the SSIS package's Control Flow tab, place a Data Flow Task and name it as Populate CompanyInfo. Double-click on the data flow task to switch to the Data Flow tab. Within the Data Flow Task, place an OLE DB Source, a Derived Transformation and an OLE DB Destination as shown in screenshot #13. NOTE: You need to configure the tasks one by one in the order shown. Don't place all the tasks at the same time and try to connect them.

  7. Configure the OLE DB source to read the Access database table as shown in screenshots #14 and #15. Configure the Derived transformation to convert the string text from Access database to Unicode as shown in screenshot #16. Configure the OLE DB Destination to insert the data into SQL table as shown in screenshots #17 and #18.

  8. Go back to Control Flow tab and place another Data Flow Task as shown in screenshot #19.

  9. In the second data flow task, we read the same Companies table in Access database and try to populate the WidgetInfo table in SQL.

  10. Place an OLE DB Source to read the Access table and configure it as hown in screenshots #20 and #21. Place a Derived transformation to convert the string to Unicode as shown in screenshot #22. Place a Lookup transformation to fetch the CompanyId based on the name and address and configure the task as shown in screenshots #23 and #24. Lookup task by default will fail if it can't find a match. We need to normalize the Widget data. So, place an Unpivot transformation and configure it as shown in screenshot #25. Place an OLE DB Destination to insert the data into SQL and configure it as shown in screenshots #26 and #27. Second data flow task would be as shown in screenshot #28

  11. Screenshots #29 - #31 show sample package execution.

  12. Screenshot #32 shows data in the SQL tables after the package execution.

I hope that this should give an idea of exporting data from Access database to SQL server. You can group the tables in Access and load them within a single data flow task. If there are tables depending on other tables, then you can place the in separate data flow task as demonstrated in this example.

Hope that helps.

SQL Scripts:

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

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

Screenshot #15:

Screenshot #16:

Screenshot #17:

Screenshot #18:

Screenshot #19:

Screenshot #20:

Screenshot #21:

Screenshot #22:

Screenshot #23:

Screenshot #24:

Screenshot #25:

Screenshot #26:

Screenshot #27:

Screenshot #28:

Screenshot #29:

Screenshot #30:

Screenshot #31:

Screenshot #32:

这篇关于如何使用 SSIS 包将 MS Access 数据导入 SQL Server?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆