Using temporary tables in SSIS

Hello,
I want to know if its possible to use temporary tables to append columns from different database tables for export to a physical table in another database.
MenoforiAsked:
Who is Participating?
 
Reza RadConsultant, TrainerCommented:
SSIS itself use temporary method,
It fetch data from a source, hold it in system memory, and transfer it to destination.

if this doesn't help you, let me know what you want to do exactly?
0
 
brad2575Commented:
Yes you can use temp tables, you will just need to check the "Use same connection" in the database connection properties if you need to do the work across more then one SSIS item/process.

Also you will need to set the properties for validation to not be at runtime.
0
 
da-zeroCommented:
Yes, you can use temporary tables, but I advise you to use global temp tables instead of local ones, as SSIS has troubles with local temp tables.

Syntax for global temp tables: ##myTempTable

As Brad2575 suggested, also set DelayValidition to False and set the RetainSameConnection property to True of the connection to your database. When you want to configure OLE DB Destination components (or Source) that use the temp table, right-click on the task that creates the temp table and select "Execute Task". This will create the temp table in memory, so you can use it in the editor screens.
(you can also create the table in management studio to make sure it exists when you are configuring your package. Just don't close the connection).

If you have more questions regarding the use of temp tables in SSIS, let me know.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.