Using temporary tables in SSIS

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.