EDIT:
I do not believe the Using temporary tables in ssis package question helps me. I can't use a stored procedure because I don't have write permissions in DatabaseA. I am toying around with even simpler code such as:
CREATE TABLE #A ( ID INT
)
INSERT INTO #A SELECT ID FROM B
select * from #A
which still gives me the metadata error. Not sure what to do
I understand this question has been asked in several varieties here, the issue is that I am not expert enough to really understand if the answers apply to my situation or not. I am decent at the SQL code, but the SSIS part is not in my wheelhouse.
High-level overview. I have data in DatabaseA. I have no write permissions in DatabaseA. There is a major issue in DatabaseA with regards to my data. There is no static unique identifier for the items I am analyzing. To do the calculations I need, I first need to do some backing-into reference data to make my own unique identifier and then perform the calculations I need. The code I have, using temp tables, and storing all final results in a temp table, can be performed in DatabaseA.
The issue is that I can't use that code in SSIS, because I get a metadata error since I use temp tables. I have tried to use CTEs, but my code in SSIS ran for 24 hrs and was still running. Using temp tables in DatabaseA gave me my results in 2.5 hrs.
Although we have our own database, DatabaseB, I can't bring in the intermediate steps there. I can't perform the calculations in DatabaseB, I have tried. Basically we don't have enough space for the calculations, though we have enough memory to store the tables. My IT team is not a help, they are inundated with other things so I need to figure this one out. If I could get the code to run as-is in DatabaseA and just have the results pulled into DatabaseB it would be fine. This is what I am trying to do with the SSIS packages. My code is something like the following where the temp tables are created to materialize smaller resultant tables.
Is there any hope for me here to get this to work in SSIS? The code works in a reasonable time if I just run it in DatabaseA and store the results in a temp table. I just need to get the over to DatabaseB
Select
*
into #A
from A where abc
Select
*
into #B
from B where dce
Select
*
into #C
from C where fgh
select
...
into #D
from #C join #A on...join (#C) #B on where ijk...
Select
...
from #D join E on... where xyz
There are more intermediate steps, but not many. But if I could get an idea of a general solution here, I think I should be able to apply it to my code. Any help is appreciated.
