将执行 SQL 任务的结果保存到 Excel [英] Saving results from Execute SQL Task to Excel

问题描述

我正在尝试在 SSIS 中开发一个包.该包的逻辑比较简单:执行一个查询并将结果集保存在一个excel电子表格中.因此，我首先尝试创建如下所示的架构:-

我面临的问题是查询使用了临时表，而 OLE DB 源似乎无法解析包含临时表(#table-names)的查询.

I'm trying to develop a package in SSIS. The package's logic is relatively simple: Execute a Query and save the result set in an excel spreadsheet. Hence I first tried to create an architecture as shown below:-

The problem I'm facing is that the query makes use of temporary tables and OLE DB Source doesn't seem to be able to parse queries containing temp tables(#table-names).

所以我尝试在我的控制流中使用执行 SQL 任务.这似乎能够执行此查询.但现在我想获取整个结果集并将其保存在动态命名的 Excel 电子表格中.我知道创建临时表是一种解决方案，但我不能使用它，因为客户不会对此感到满意.我试过将结果集保存在一个变量中，但即使我无法从 OLEDB 源中读取.任何人都可以请给我建议.如果您需要任何说明，请发表评论.

So i tried using a Execute SQL task in my control flow. This seems to be able to execute this query. But now I want to obtain the entire result set and save that in an Excel spreadsheet that is named dynamically. I understand creating a temp table is a solution, but I cannot use that as the client wont be happy about it. I've tried saving the result set in a variable but even that I cannot read from the OLEDB Source. Can anyone please give me suggestions on this. If u want any clarifications, do comment.

谢谢

推荐答案

这可以使用定义的连接管理器的RetainSameConnection"属性来实现.(当你创建临时变量时，临时变量在连接关闭时被删除......)

this can be achieved using "RetainSameConnection" property of the connection manager defined. (when you cretae the temp var the temp var is delete when the connection is closed...)

这篇关于将执行 SQL 任务的结果保存到 Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！