Importing Excel File in SSIS

Okay... I am now about to pull my hair out: something that worked VERY EASILY in Server 2000 doesn't seem to work at all in 2005. I am trying to pump an Excel table into a 2005 database. I go into the Visual Studio Integration Services Project (this is so much easier... cynicism) and set up a project. I have my data source (Excel), I have my destination (SQL Natve Server, database). I set it up the same way that it worked (perfectly) in DTS and I run it... it grinds away and reports back that all is well... no errors. I go looking for the table... not there. I try with an SA login VS windows authentication... not there. I try with a different table... no there. I try with a different database... not there.

I am certain you can imagine the frustration... that is, if you are a user, not a programmer at Microsoft.

Okay, okay... I won't launch in to abuse here... but hey, how do I make this very complicated process now work?

Thanks...

Use the OLE DB Destination.

The SQL Server destination is for databases local to the package.|||

Thanks Phil... I did figure it out with more fidling (as is usually the case, when you breakdown and ask for help, you discover the answer and just how much of a dolt you are): you have to SAVE the package before it runs correctly. [so, what was previously a ONE STEP operation, is now 8 steps... joy, joy, joy]

Thanks, and adios...

Importing Excel data into SQL Server 2005 with a twist!

Hi,

I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.

However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.

So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.

TIA,

Graham.

I would approach the problem by importing all of the spreadsheet rows and then process to the required table.