SSIS 导入文件与直接从源检索数据
我们希望将数据从位于另一个国家/地区的公司 WAN 中其他位置的源位置导入到 SQL Server 数据库中。
我们将使用 SSIS 来执行导入，但想知道哪里是执行提取和转换的最佳位置。我们可以在源 SQL 服务器上创建一个视图，SSIS 将直接从中检索数据。另一种方法是从源中删除一个文件，并让 SSIS 从该文件导入数据。
我认为前者是一个更干净的解决方案，但有兴趣知道使用文件是否有任何好处或直接获取数据是否有潜在问题？
谢谢
We wish to import data into a SQL Server database from a source location located elsewhere in the company WAN in another country.
We are to be using SSIS to perform the import but wonder where would be the best place to perform the extract and transform. We could create a view on the source SQL server and SSIS will directly retrieve data from that. The alternative would be to drop a file out of the source and have SSIS import the data from that file.
I am thinking the former is a cleaner solution but would be interested to know whether there are any benefits in using files or potential issues with grabbing the data direct?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果可能的话，我会避免使用文件，特别是如果您的起点是数据库。通过提取到文件，您将在过程中添加不必要的层，这会增加出错的可能性。使用提取的文件的典型问题包括无意中使用旧的/不完整的文件（如果提取失败）和屏蔽用户手动编辑的更改（直接在文件中处理数据问题）。
如果您有 SQL Server 数据库，那么创建存储过程、视图或在 SSIS 中输入 sql 将为您提供源和 SSIS 之间定义的接口。将转换与提取一起包含确实会使界面有点模糊，但对于不依赖于任何目标（或辅助源）数据进行加载的简单转换来说，这是很常见的。
获取数据（使用任一方法）时可能需要考虑的一个问题是数据的事务状态。根据您的来源，您可能需要处理各种完整性状态的数据并采取适当的行动。
I would avoid using files if possible, especially if your starting point is a database. By extracting to a file, you would be adding an unnecessary layer in the process that would increase the possiblity of errors. Typical issues of using extracted files include unwittingly using an old / incomplete file (if extract failed) and masking user manually edited changes (direct in file for data issues).
If you have a SQL Server database, then creating a stored procedure, view or entering sql into SSIS would give you defined interface between source and SSIS. Including the transform with the extract does blur the interface a little, but is quite common for simple transformation that do not depend on any target (or secondary source) data for the load.
An issue you may need to consider when grabbing data (with either approach) is the transactional state of data. Depending on your source, you may need to handle data in various states of completeness and act appropriately.