SQL SSIS Help. Import an excel sheet into a temp table

I have a farily simple task of taking an Excel sheet and importing it into a SQL 2005 database table. I need to create an SSIS task for this. The Excel sheet does not have all the columns I need to make the insert directly into the permanent sql table, but I know how I could link out to other tables and get the columns that are missing. So I was wondering how I could import the Excel sheet into a #tempTable (or @VariableTable) and then one in a temp table I could just write my SQL Insert code (using the temptable as well as the other tables that I will link on) in a basic Execute SQL Task. But I am having trouble figuring out how to do this with SSIS. When 开发者_StackOverflow中文版I drag my excel source and try to link it to a SQL Server Destination the drop down doesn't have an option for temptables.

The SSIS way of doing this would be to use a Merge or Lookup transform. I don't think that you can put things into a temp table like that, but you could have an ExecuteSQL task that creates an actual table that you can then drop at the end of the package. You can then have your package use that.

During the design time you might need to have the table in place to link things up, but it shouldn't need to be there when you actually run the package.

First, you'll need to create the staging table for the excel worksheet. Open SSMS, right click the database, choose tasks, import data. Set the import source as excel. Browse to the file. Set the destination as SQL Server. You can accept the table name or name it as you wish. I suggest naming it something useful. Depending on your understanding of data types and what is in the excel sheet, it may take you a while to get this right. Eventually, you will have a table that will accept the contents of the excel sheet.

Second, create your ssis package by using an excel source and sql serve or oledb destination.

1. take Execute SQL task in control flow to create the target staging table of excel sheet surce.

2. in data flowuse excel source and oled db target to staging table create in the first step.

3.In control flow use merge or join statement to your excel targeted staging table with other source table to final target table.

thanks prav