Using temp tables in SSIS

Question

I've created an ADO.NET connection manager, and a DataReader source with the following SQL Command:

select 
   'test' as testcol
INTO
   #tmp

select * from #tmp

If I click the refresh button in the DataReader component, I get SqlException "Invalid object name #tmp". The SQL statment itself is clearly valid and executes properly in sql server management studio. I've also tried setting DelayValidation on the connection manager, to no avail.

Answer 1

is the error on the INSERT or the SELECT?

if you are issuing only one command that contains both the INSERT and SELECT, try putting a semicolon before the SELECT.

EDIT after OP comment
encapsulate all the logic within a stored procedure:

CREATE PROCEDURE YourProcedureName
AS
select 
   'test' as testcol
INTO
   #tmp

select * from #tmp

GO

the have your application run this single SQL command:

exec YourProcedureName

EDIT after next OP comment

OP doesn't say which SQL Server version they are using, if 2005 or up, try a CTE:

;with CTEtemp as
(
select 
   'test' as testcol
)
select * from CTEtemp

Answer 2

Why couldn't this be replaced with a "SELECT 'test' as testcol"? The SSIS query parser may be having trouble with it because there's a temp table involved and it expects a single statement, not an actual SQL script. Or, if what you're sharing above is only an example for illustration, maybe something like this:

SELECT *
  FROM (SELECT 'test' AS testcol)

Can you elaborate on what you're trying to accomplish here and, if it is, why the temp table is required?