SSIS 中的记录集操作
在我的 SSIS 工作中，我需要积累一组行，并在处理成功完成后过渡性地提交它们。如果这是纯 SQL，我会在事务中使用临时表。在 SSIS 中，存在许多使这一情况变得复杂的问题。让多个组件共享同一个事务是很困难的，并且在设计时拥有不存在的临时表是一件痛苦的事情。
如果我在 SSIS 中使用记录集来实现此目的，则会出现其他问题。我的理解是，“执行 SQL”组件将在运行时重新初始化记录集，因此我不能使用它来附加附加行。有没有办法创建引用内存记录集的 OLE DB 连接？
有没有更好的方法来达到这个结果？
In my SSIS job, I have a need to accumulate a set of rows and commit them all transitionally when processing has completed successfully. If this was pure SQL, I would use a temp table inside a transaction. In SSIS there are a number of issues complicating this. It's difficult to have multiple components share the same transaction and having temp tables that do not exist at design time is a pain.
If I use Recordsets inside SSIS for this purpose, there are other issues. My understanding is that an 'Execute SQL' component will re-initialize the Recordset when it runs, so I can't use that to append an additional row. Is there a way to create an OLE DB connection that references an in-memory Recordset?
Is there a better way to achieve this result?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
有一个更好的方法。我建议使用临时表，正如您所说您已经尝试/考虑过。在 SSIS 中使用临时表的关键是：
1. 您需要在连接管理器上将“保留相同连接”属性设置为 true，否则“智能”连接管理和池将最终在包中途删除您的连接（并在没有临时表的情况下恢复它）。
2. 您需要使用已创建的临时表来设计包 - 即使您必须将其创建为常规表才能进行设计。当您准备好部署时，更改涉及它的组件中的表名称，并将这些任务的 DelayValidation 属性设置为 False。 （这将防止包失败，因为包最初启动时该表不存在。）
There is a better way. I'd suggest using a temp table, as you said you'd tried/considered. The keys to using a temp table with SSIS are:
1. You need to set the "retain same connection" property to true on the Connection Manager, otherwise the "smart" connection management and pooling will end up dropping your connection (and restoring it without the temp table) midway through your package.
2. You need to design your package with the temp table created already - even if you have to create it as a regular table in order to design against. When you're ready to deploy, change the table name(s) in the components that touch it, and set those tasks DelayValidation property to False. (That will prevent the package from failing because the table doesn't exist when the package is initially started.)