How can you use a #temp table from one SSIS task in another task step?

Posted on 2009-04-17
Last Modified: 2013-11-10

I'd like to create a #Temp table as part of join in a SQL statement in a OLE DB Source task step.  I keep getting an error as the # temp is not available at the time I build the new SQL OLE DB Source.  

I've tried ## temp tables and had no luck setting a SQL Task to use Full Result Set.

Any thoughts?

Question by:ToddFields2
    LVL 5

    Expert Comment

    Global temporary tables are deleted when all users referencing the table disconnect from Microsoft SQL Server.  I'm not sure if this is your problem or not.  Why not try using a regular table and deleting it manually?
    LVL 5

    Expert Comment

    Edit: of course, by manually I meant by manually adding a line of code to do it automatically...

    Author Comment


    Yeah, that is my option.  The issue would be that the table has to continue to exist (ie. I would truncate it and not drop it) so that the next OLE DB SQL would correctly compile.

    LVL 5

    Accepted Solution

    Yeah, the point (one of them anyway) of temporary tables is that they automatically get deleted, so if you need it to persist then that's what you'll need to do.  If you just didn't want to add it to the database your working with you could always create a seperate db for it though.  Sometimes I do that for miscellaneous tables instead of using temp tables anyway just so that I can access them through the ide.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now