用 PowerShell 替换 SSIS？
有些人不喜欢SSIS，原因如下，
- 当设计稍微复杂一点的包时，需要找到并单击分散在不同地方的快速替换。
- 这些合并、查找组件的性能不佳。我听说很多顾问只是建议在 SQL Server 表中加载数据并使用 transact-sql。
我在一个小项目中使用了 powershell，它导出数据并创建 csv 文件。我使用过 powershell 并且喜欢它。用 Powershell 取代传统上使用 SSIS 的一些任务是否是一种趋势？特别是在仅出口的情况下？
Some people don't like SSIS for the following reasons,
- Need to find and click the express replacement scattered in different place when design a little bit more complex package.
- These merge, lookup components don't perform well. I heard a lot of consultants just recommend loading data in the SQL Server tables and use transact-sql.
I've used a powershell in a small project which export the data and create csv files. I've used powershell and like it. Is it a trend to replace some of the tasks traditionally using SSIS with Powershell? Especially in the export only cases?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
对于非常小的项目/任务，power shell 是一个不错的工具。
对于需要健壮、可维护、模块化、处理错误和审计的项目，SSIS 非常优越。
事实是，太多的 SSIS 实现是由不了解程序优势的开发人员精心设计的。他们只是尝试以最小的努力或利用其功能将当前的 T-SQL ETL 流程复制到 SSIS 中。性能问题几乎总是与此相伴。
SSIS 不仅仅是让 SP 和 TSQL 自动运行的 GUI 方式。如果你真的想了解更多关于这个主题的知识，我建议你读几本书——仔细聆听窄领域专家的意见；他们的技能很容易失去相关性，并让其他人落后于他们。
Powershell 趋势远离 SSIS？离它重要的地方并不远。
For very small projects/tasks power shell is an ok tool.
For projects that need to be robust, maintainable, modular, handle errors and auditing, SSIS is vastly superior.
The truth is, too many SSIS implementations are crafted by devs that don't understand the strengths of the program. They simply try to replicate their current T-SQL ETL process into SSIS with minimal effort or leverage of its capabilities. Performance issues almost always go right along with this.
SSIS is Not just a GUI way to get SPs and TSQL to autorun. If you really want to learn more on the subject I suggest picking up a few books - careful listening to narrow-fielded experts; their skillsets can easily fade from relevance and keep others behind with them.
Powershell trend away from SSIS? Not anywhere close to where it counts.
这是一个老话题，但我觉得很值得讨论。因此，我想谈谈为什么我认为 SSIS 在 99% 的情况下作为 ETL 工具是一个糟糕的选择。
此时，我认为SSIS唯一比PowerShell更好的是它在处理具有多个源/目标的大量数据时的性能，这主要归功于SSIS内部的并行性和缓存能力。
然而，SSIS因其错误消息而臭名昭著，一旦部署了SSIS包，几乎无法调试，而且从源代码控制的角度来看，SSIS包是XML文件，很难在版本之间进行比较，一旦源或目标对象也非常脆弱有微小的变化（例如目标上的一列增加了一个字符）。
在我的prod环境中，有很多SSIS包是用sql代理作业部署和调度的，所以当作业失败时，我没有办法找出问题所在，直到我去TFS找到SSIS项目并打开它在 Visual Studio 中找出逻辑。这是一场噩梦。
使用PowerShell，你看到的代码就是执行的代码，你总是可以从PS代码中获取逻辑并一路进行故障排除。
如今，随着越来越多的开源 PS 模块的出现，PowerShell 的威力呈指数级增长，确实是时候考虑使用 PS 作为替代工具而不是 SSIS 了。
This is an old topic but I find it is well worth discussing. So I'd like to put a few ideas why I think SSIS is a bad choice 99% of the time as a ETL tool.
At this time, the only thing I can think of SSIS better than PowerShell is its performance in handling huge amount of data with multiple sources / targets, this is mainly due to the SSIS internal parallelism and caching capability.
However, SSIS is notorious for its error message, almost unable to debug once SSIS packages are deployed, also from source control perspective, SSIS packages, which are XML files, are difficult to compare between versions, also very fragile once either source or target objects have minor changes (like a column on target is increased by one char).
In my prod environment, there are many SSIS packages deployed and scheduled with sql agent jobs, so when there is a job failure, there is no way for me to figure out the problem until I went to TFS to find the SSIS project and open it in Visual Studio to figure out the logic. It is a nightmare.
With PowerShell, the code you see is the code executed, and you can always get the logic from the PS code and do the trouble-shooting along the way.
With many, many open-sourced PS modules these days, PowerShell's power is increasing exponentially, it is indeed the time to consider using PS as an alternative tool rather than the SSIS.