1

I have a SSIS dataflow task. Within this is an OLEDB source with SQL command text as follows:

SELECT * 
FROM ##TMP_TBL1

This works provided that during development I create that temp table via management studio (otherwise it won't allow me to save the SQL command text). This works fine.

When I try the same with local temporary table, it doesn't work. Is there any way to use local temporary table inside the SSIS dataflow's OLEDB source SQL command text?

variable
  • 8,262
  • 9
  • 95
  • 215
  • Why do you need a temporary table in the first place? What problem are you trying to solve by using one? – Thom A Dec 31 '21 at 13:46
  • I have a users table in the DW. I want to fetch oltp data only for these users. Presently I have created global temp table on the oltp db. Then using data flow task for fill it (dw->oltp temp table). Then in subsequent data flow task im joining oltp table to temp table to fetch required data. – variable Dec 31 '21 at 16:12
  • So are you "filling" it in a separate task? If so, then by the time the next task is run the temporary table will already be dropped. Having a single statement like that is almost certainly going to fail when you try to run it in SSIS and the scope the temporary table was created in had most likely ended. We don't have the full picture here. – Thom A Dec 31 '21 at 16:15
  • In the connection I have set retain same connection as true. So the table isn't dropped until the entire package completes. How else can I only pull specific records from oltp table? – variable Dec 31 '21 at 17:18
  • What's wrong with a `WHERE`? – Thom A Dec 31 '21 at 17:38
  • Let me say again- dw has user table. Say it has 10 users. Where as oltp has user table with 10000 users. I want to pull sales only for 10 users. So Im loading these 10 users from dw table into temp table on oltp db. Then using that temp table to join and get only sales for thise 10 users. Hope makes sense. This saves me from having to pull sales for all 10k users and then filtering on dw. – variable Dec 31 '21 at 17:43
  • You need to think differently. SSIS is designed to do things in a particular way but you choose a path that is not well-suited to that design. Your goal is still not clear since "pull sales for x users" can be interpreted in different ways. Perhaps searching for "ssis execute a task for each row" is a better starting point. – SMor Dec 31 '21 at 19:00
  • 10 was an example, if the number is 100 then I don't want to use for example the for each enumerator when I can use temp tables a more quicker approach. – variable Dec 31 '21 at 19:01

0 Answers0