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

views:

answers:

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

Tom H. 2010-06-24 19:03:20

What would I start with? Do I still use an Excel source? The only thing I can do so far is just a direct import from Excel to a SQL table without any modifications or linking at all?

Timothy S 2010-06-24 21:38:28

Yes, you would start with an Excel source. If you want to create a table in the package, just add an Execute SQL Task before your data transform task to create it and another at the end to clean it up. If you are going to use the Merge Join or Lookup you would use the Excel source as one input and your SQL table as another for the transform. That's all done within your data transform task.

Tom H. 2010-06-25 01:28:32

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.

MaasSql 2010-06-24 21:55:43

take Execute SQL task in control flow to create the target staging table of excel sheet surce.
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

praveen 2010-06-24 22:11:37

But the #tmpTable created in step 1 isn't in the drop-down list to select from in step 2 oled db target table.

Timothy S 2010-06-24 22:49:33

Use derived table of the structure same as excel source insted and drop that at the end of the process using sompletion status on precedence constraint. #table is not an ideal because of its scope and also not recomended due to parallel processing.

praveen 2010-06-24 23:15:19

ansaurus

tags:

views:

answers:

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

related questions