SSIS ##tmptable is dropped when execute SQL task is executed

Question

Before you say me, I put retainsameconnection, I had put.

and I have delay validation on ALL my components too

after in others query where I use this dynamic temp table this fails (sometimes) saying temp table does not exist.

How can I solve it?

This is my query..

declare @usuario varchar(100)
declare @contra varchar(100)
declare @servidor varchar(100)
declare @based varchar(100)
declare @nombretabla varchar(100)

set @usuario ='pruebas'
set @contra ='123'
set @servidor ='1.6.7.9'
set @based ='op'

set @nombretabla='tablaBC245B7A910D4B488CC9EFF0EFD3C177'

/*this was a dynamic name got with this query in other execute sql task
declare @tabla varchar(50)
set @tabla=NEWID ()
set @tabla =REPLACE (@tabla ,'-','')

select 'tabla'+@tabla  nombre
*/

declare @cadena varchar(500)
set @cadena ='SERVER='+@servidor+';DataBasE='+@based +
  ';uid='+@usuario +';pwd='+@contra+';'

set @cadena =''''+@cadena +''''

while OBJECT_ID('tempdb..##envases'+@nombretabla) IS  NULL  
begin

    exec('
        SELECT * into ##envases'+@nombretabla+'
        FROM OPENROWSET(
            ''SQLNCLI'', 
            '+@cadena+',
            ''
            select pro=p.pro,envase=e.pro from procorp p 
            join procorp e on p.envase=e.envase and e.esenvase=1 
            union all select pro=-1,envase=-1
             '')      
         ' 
         )  
end

IF OBJECT_ID('tempdb..#primera') IS NOT NULL DROP TABLE #primera
IF OBJECT_ID('tempdb..#end') IS NOT NULL DROP TABLE #end

create table #primera(
  pro int,
  envase int
)

insert into #primera
exec('select * from ##envases'+@nombretabla+'')

select fila='select pro='+CONVERT(varchar(10),pro)+
  ', envase='+CONVERT(varchar(10),envase)+' union all ' 
into #end
from #primera

--select * from #end
--drop table #end
--drop table #primera

alter table #end
add 
uno int not null default(1),
id int identity

declare @maximo int
set @maximo =(select MAX(id) from #end )

update #end set fila=fila+'terminado' where id=@maximo
update #end set fila=replace(fila,'union all terminado','') where id=@maximo 

select uno, '  begin try drop table #tmpenvase end try begin catch  end catch  
  select * into #tmpenvase from ( '+ 
  convert(varchar(max),STUFF(
    (select  '', convert(varchar(max),fila) from #end  order by id
      for xml path('')  )
   ,1,0,'')         )+' ) q 'cadena
from #end e 
group by uno

Answer 1

I would use standard SSIS objects for this. I cant really follow what the end objective of your script is, but generally:

Connection strings can be managed using Configuration files, Variables and other means. Within Data Flow Tasks, OLE DB Source Transformations can run SELECT statements, OLE DB Destinations can deliver data, OLE DB Commands can run UPDATE statements.

I would reimagine your requirements in terms of a series of tasks, rather than trying to force everything into a single T-SQL script.

Answer 2

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

According to that paragraph of SQL Server Books-Online; as soon as SQL Server don't found any connection to a global temporary tables those temporary tables will deleted, and when you are create a local temporary table with EXEC command SQL Server will delete that local temporary table this will also applied to a global temporary table but after a checking for all users connections and so on. I think sometimes SQL Server found your global temporary table alone before the your next statement commits.

I suggest you to use local table variables and use INSERT EXEC to fill them.

And also there are some other ways too.