SSIS 能否很好地导入多个表？
直到明天我才能访问 SSIS，所以我想在开始这个项目之前征求建议。
我们目前使用 Access 来存储我们的数据。它没有以关系格式存储，因此非常混乱。我们想要迁移到集中式数据库（SQL Server 2008 R2），这需要重写我们的大部分代码库（顺便说一句，这也是一团糟。）由于时间限制，在这之前我们将需要建立一个集中数据库，专门用于为客户按需生成报告。因此，我们的应用程序仍将在 Access 上运行。而不是：
接收数据->导入到具有一张表的 Access 初始文件 ->数据处理->用一张表访问结果文件->报告生成
目标是：
接收数据->导入到具有一张表的 Access 初始文件 ->将初始数据导入到 SQL Server 中的多个表 ->使用一张表导出 Access 工作文件 ->数据处理->访问结果文件->将结果导入到 SQL Server 中的多个表 -> 时生成报告
每当我们打算使用 SSRS 作为报告组件 ，这看起来很简单。我不确定 SSIS 本身是否可以很好地将 Access 数据拆分为多个表，或者是否应该将所有内容导入到 SSIS 的临时表中，然后使用存储过程拆分，或者我是否需要编写一个独立的应用程序。
之前没有做过太多关于 SQL Server 的工作，因此非常感谢您的建议。
I won't have access to SSIS until tomorrow so I thought I'd ask for advice before I start work on this project.
We currently use Access to store our data. It's not stored in a relational format so it's an awful mess. We want to move to a centralized database (SQL Server 2008 R2), which would require rewriting much of our codebase (which, incidentally, is also an awful mess.) Due to a time constraint, well before that can be done we are going to need to get a centralized database set up solely for the purpose of on-demand report generation for a client. So, our applications will still be running on Access. Instead of:
Receive data -> Import to Access initial file with one table -> Data processing -> Access result file with one table -> Report generation
The goal is:
Receive data -> Import to Access initial file with one table -> Import initial data to multiple tables in SQL Server -> Export Access working file with one table -> Data processing -> Access result file -> Import result to multiple tables in SQL Server -> Report generation whenever
We're going to use SSRS for the reporting component, which seems like it'll be straightforward enough. I'm not sure if SSIS alone would work well for splitting the Access data up into numerous tables, or if everything should be imported into a staging table with SSIS and then split up with stored procedures, or if I'll need to be writing a standalone application for this.
Haven't done much of any work with SQL Server before, so any advice is appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在SSIS包中，您可以编写代码（例如C#）来执行您自己的/自定义的数据转换。但是，SSIS 附带内置转换，可能适合您的需求。 SSIS 非常强大且灵活。实际上，您可以对 SSIS 中的数据执行几乎任何您想要的操作。
您的任务的高级工作流程可能如下所示：
1.连接数据源并拉取数据
2. 转换数据
3.输出数据到目标数据源
In SSIS package, you can write code (e.g. C#) to do your own/custom data transformations. However, SSIS comes with built-in transformations that may be good for your needs. SSIS is very powerful and flexible. Actually, you may do pretty much anything you want with the data in SSIS.
The high level workflow for your task could like like the following:
1. Connect to the data source and pull the data
2. Transform the data
3. Output data to the destination data source
您当然可以将数据流分成两个单独的分支并将其发送到两个目的地。您需要做的就是在数据流中放置一个多播，然后大部分转换就会发生。
然而，根据您所说，更好的解决方案可能是使用 Access 表作为临时数据库，然后从那里获取数据并将其发送到 SQL Server。这意味着两个数据流，但它将是一个更干净的实现。
You certainly can split a data flow into two separate branches and send it to two destinations. All you need to do is put a multi-cast in the dataflow and then the bulk of the transformations will happen after that.
From what you've said, however, a better solution might be to use the Access tables as a staging database and then grab the data from there and send it to SQL Server. That would mean two data flows but it will be a cleaner implementation.