为什么我要在 SQL Server 2008 中使用 SSIS 包而不是其他技术？
我在内部开发小组的质量保证部门工作。我们的生产数据库程序员一直在构建一个 SSIS 包，以从各种数据库位创建加载文件，以便导入到第三方应用程序中（我们正在测试与此的集成）。
构建完成后，很快发现它对创建它所用的 SQL Server 和 Visual Studio 版本有依赖，并且对生产环境也有相当少的依赖（这不是 SSIS 问题，只是描述了本质）我们的设置）。
构建这个项目需要几天的努力，然后无法在我们的 QA 环境下运行。
在向该团队询问他们的包正在运行的 SQL 查询（它在生产环境中运行良好）后，我编写了一个 python 脚本来执行相同的任务，而无需任何依赖项。我花了两个多小时（请注意，我已经有了一个用于处理数据库交互的自定义库），并且我能够写出我需要的 UTF-16LE 文件。
现在，我们的生产数据库程序员不是 SSIS 专家，但他们在工作流程中使用了相当多的 SSIS —— 我很乐意称他们所有人都胜任自己的职位。
因此，我的问题是——考虑到它所花费的时间以及对 SQL Server 和 Visual Studio 版本的依赖性，SSIS 包带来了哪些我在 python 代码中可能看不到的优点或好处？或者 shell 脚本、Ruby 或当前的代码风格？
I'm in a QA department of an internal development group. Our production database programmers have been building an SSIS package to create a load file from various database bits for import into a third-party application (we are testing integration with this).
Once built, it was quickly discovered that it had dependencies on the version of SQL Server and Visual Studio that it was created with, and had quite of few dependencies on the production environment as well (this is not an SSIS problem, just describing the nature of our setup).
Getting this built took several days of solid effort, and then would not run under our QA environment.
After asking that team for the SQL queries that their package was running (it works fine in the production environment), I wrote a python script that performed the same task without any dependencies. It took me a little over two hours (note that I already had a custom library for handling our database interaction), and I was able to write out a UTF-16LE file that I needed.
Now, our production database programmers are not SSIS experts, but they use it a fair bit in their workflows -- I would readily call all of them competent in their positions.
Thus, my question -- given the time it appears to take and the dependencies on the versions of SQL Server and Visual Studio, what advantage or benefits does an SSIS package bring that I may not see with my python code? Or a shell script, or Ruby or code-flavor-of-the-moment?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
无论如何，我不是 SSIS 专家，而是一个拥有 SSIS 工作经验三年多一点的普通开发人员。与任何其他软件一样，SSIS 也有缺点，但到目前为止我很喜欢使用 SSIS。技术的选择取决于个人的要求和偏好。我并不是说 SSIS 优于其他技术。另外，我没有使用过 Python、Ruby 或你提到的其他技术。
这是我的两分钱。请对此持保留态度。
从普通开发人员的角度来看，一旦您了解了如何处理 SSIS 的细微差别，它就很容易使用。我相信任何其他技术也是如此。 SSIS包是可视化工作流程而不是编码工具（当然，SSIS也具有出色的编码能力）。通过查看工作流程，您可以轻松了解包中发生的情况，而不是查看数百行代码。
SSIS 的构建主要是为了执行 ETL（提取、转换、加载）作业。它经过精心调整，可以很好地处理该功能，尤其是与 SQL Server 一起使用时，更不用说它还可以处理平面文件、DB2、Oracle 和其他数据源。
您只需很少的编码或无需编码即可执行大部分任务。它可以在几分钟内将数百万行从一个数据源加载到另一个数据源。 查看此示例 演示一个包，该包可在 3 分钟内将制表符分隔文件中的一百万行加载到 SQL Server 中。
日志记录功能可捕获包及其任务执行的每个操作。它有助于查明错误或跟踪有关包执行的操作的信息。这不需要编码。 查看此示例 用于日志记录。检查点有助于像记录器一样捕获包执行，并协助从失败点重新启动包执行，而不是从头开始运行包。表达式可用于根据给定条件确定包流。包配置。 查看此示例 用于基于环境变量的配置。第 #4 - #7 点是开箱即用的功能，需要少量配置，并且根本不需要编码。SSIS 可以利用 .NET 框架功能，如果开发人员找不到满足其要求的组件，他们也可以创建自己的自定义组件。 查看此示例以了解如何将 .NET 编码与不同的数据源结合使用。此示例的创建时间不到
3 小时。SSIS可以使用相同的数据源进行多次转换，而无需重新读取数据。 查看此示例 了解多播的含义。这是示例 如何处理 XML 数据源。
SSIS 还可以轻松地与 SSRS（报告服务）和 SSAS（分析服务）集成。
我刚刚列出了我在 SSIS 中使用过的非常基本的东西，但还有很多不错的功能。正如我之前提到的，我不确定 Python、Ruby 或其他语言是否可以如此轻松地处理这些任务。
这一切都归结为人们对技术的舒适度。当技术是新技术时，人们非常怀疑并且不愿意适应它。
根据我的经验，一旦您了解并接受 SSIS，它确实是一项很好用的技术。它与 SQL Server 配合得非常好。我不否认我在开发包的过程中遇到了障碍，但大多数都找到了克服它们的方法。
这可能不是您期待的答案，但我希望这能给您一个想法。
I am not an expert in SSIS by any means but an average developer who has experience working with SSIS for little over three years. Like any other software, there are short comings with SSIS as well but so far I have enjoyed working with SSIS. Selection of technology depends on one's requirement and preferences. I am not going to say SSIS is superior over other technologies. Also, I have not worked with Python, Ruby or other technologies that you have mentioned.
Here are my two cents. Please take this with a grain of salt.
From an average developer point of view, SSIS is easy to use once you understand the nuances of how to handle it. I believe that the same is true for any other technology. SSIS packages are visual work flows rather than a coding tool (of course, SSIS has excellent coding capabilities too). One can easily understand what is going on within a package by looking at the work flows instead of going through hundreds of lines of code.
SSIS is built mainly to perform ETL (Extract, Transform, Load) jobs. It is fine tuned to handle that functionality really well especially with SQL Server and not to mention that it can handle flat files, DB2, Oracle and other data sources as well.
You can perform most of the tasks with minimal or no coding. It can load millions of rows from one data source to another within few minutes. See this example demonstrating a package that loads a million rows from tab delimited file into SQL Server within 3 minutes.
Loggingcapabilities to capture every action performed by the package and its tasks. It helps to pinpoint the errors or track information about the actions performed by the package. This requires no coding. See this example for logging.Check Pointshelp to capture the package execution like a recorder and assists in restarting the package execution from the point of failure instead of running the package from the beginning.Expressionscan be used to determine the package flow depending on a given condition.Package configurationscan be set up for different environments using database or XML baseddtsconfigfiles or Machine based Environment variables. See this example for Environment Variables based configuration. Points #4 - #7 are out-of-the-box features which require minor configuration and requires no coding at all.SSIS can leverage the .NET framework capabilities and also developers can create their own custom components if they can't find a component that meets their requirement. See this example to understand how .NET coding can be best used along with different data source. This example was created in less than
3 hours.SSIS can use the same data source for multiple transformations without having to re-read the data. See this example to understand what Multicasting means. Here is an example of how XML data sources can be handled.
SSIS can also integrate with SSRS (Reporting Services) and SSAS (Analysis Services) easily.
I have just listed very basic things that I have used in SSIS but there are lot of nice features. As I mentioned earlier, I am not sure if Python, Ruby or other languages can handle these tasks with such ease.
It all boils down to one's comfort with the technology. When the technology is new, people are very much skeptical and unwilling to adapt it.
In my experience, once you understand and embrace SSIS it is really a nice technology to use. It works really well with SQL Server. I don't deny the fact that I faced obstacles during development of my packages but mostly found a way to overcome them.
This may not be the answer that you were expecting but I hope this gives an idea.