Saving results from Execute SQL Task to Excel

Question

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).

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.

Thanks

Answer 1

I would move the temporary table logic to a Common Table Expression (CTE), e.g.

WITH CTE_temp AS (
<< select statement that populates your temporary table >>
)
SELECT
<< select statement from your temporary table>>
FROM CTE_temp

CTEs can be built ontop of other CTEs, and can be recursive. With some lateral thinking they cover 99.9% of temporary table solutions.

CTEs can run in parallel without contention issues. They are usually faster than temp table solutions also.

Answer 2

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...)