How to create a temporary table in sql 2005 via SSIS

105 pts.
Flat files
SQL 2005
SQL tables
I need to load a flat file into a temporary table. So I need to know how to step by step how to in SSIS "Data Flow Task" create a temporary table, load one col from the flat file to the temporary table along with the date it was loaded. I will then use the temporary table to delete from another table. Can you help this Newbee???? Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

You won’t be able to use temporary tables with SSIS. Temporary tables are only good for the length of the session. Once the session ends (IE, you disconnect) the table is dropped. Each widget you add to the SSIS package is it’s own connection to the SQL Server. You will want to create a normal table and simply setup the job to truncate the table before loading the data into it, then use that table to handle your delete.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • NewBee7
    I understand the concept of temporary tables and just to be sure, it is the task which does the connection and disconnect not the package. If the above is true, then what is the best way to create a normal table - "execute sql task" or a stored procedure? Also, in your answer could you please answer the how to create a temporary table? I am assuming that the create of the temporary table should be done within a stored procedure, because we would want to do processing that would use the temporary table before the disconnect. I may need to do this for a different project. Thanks.
    105 pointsBadges:
  • Denny Cherry
    Correct, each task makes it's own connection to the database. If the package made the connection only one task at a time could run. With each task making its own database connection more than one task can run at a time. You can do it either way. Neither one has any benefit over the other. However if you do not have rights to create physical tables if you use a stored procedure you can use the EXECUTE AS command so that the procedure runs as dbo which will then allow you to create the table. If permissions are not a problem I would probably just put the CREATE TABLE command within an Execute SQL Task object.
    69,145 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: