SSIS 2005 Import Excel (xlsx) file

Question

SSIS 2005 Import Excel (xlsx) file

SqlSavvy

SSCrazy

Points: 2641
More actions
July 29, 2010 at 8:50 am

#139941

Hi,
I have xlsx file that I need to load it into SQL server 2005 table. When I create a Excel connection source (in SSIS 2005) and select my xlsx file, I get 'External Table is not in Expected Format' message. Any idea why this is happening?
Any help would be appreciated.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jason Marshall SSCommitted Points: 1791 More actions · Answer 1

I don't think SSIS 2005 supports xslx. Is this an Excel 2007 file? Have you tried saving it as a .xls file and importing it?

Sam Greene SSCertifiable Points: 5252 More actions · Answer 2

See this article:

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64585/

Jim Jao SSC Enthusiast Points: 113 More actions · Answer 3

Here are my steps after almost an hour research online and trial-and-error:

System Config: Windows Server 2008 R2 (x64), SQL 2005 SP3 (x64) with Visual Studio 2005 SP1

1. Install AccessDatabaseEngine.exe (x86) to get Microsoft Office 12.0 Access Database Engine OLE DB Provider

2. In BIDS\package.dtsx, create a New OLE DB connection using SQL Native Client

3. Click All button, and type "Excel File Connection" in Data Source property, which will be replaced later. Click OK to make this connection

4. Select this Excel File Connection, and go to its Properties.

5. Change/replace the ConnectionString into the following example:

Data Source="\\RemoteFileServerName\InputFiles\Excel Import Data.xlsx";Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0;HDR=YES;IMEX=1;";

6. Use an OLE DB Source, and select this Excel File Connection. Click on the Name of the table or the view, a list of worksheets should be showing.

Jim Jao

raghavender.2369 SSC Eights! Points: 833 More actions · Answer 4

col1 col2 col3

5.06.03.5

9.59.86.3

9.06.08.0

Col4col5col6

9.56.08.0

6.39.33.9

8.78.08.8

9.08.36.9

8.69.06.2

I have data like this in excel, how to load into 2 different tables

first table contain col1,col2,col3as columns

second table contain Col4,col5,col6 as columns.

Note: Here the number of columns are fixed(3).

plz help me how to do it...

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

raghavender.2369 (11/30/2012)
col1 col2 col3
5.06.03.5
9.59.86.3
9.06.08.0
Col4col5col6
9.56.08.0
6.39.33.9
8.78.08.8
9.08.36.9
8.69.06.2
I have data like this in excel, how to load into 2 different tables
first table contain col1,col2,col3as columns
second table contain Col4,col5,col6 as columns.
Note: Here the number of columns are fixed(3).
plz help me how to do it...

Do not hijack other peoples threads, you have already created your own thread here, please be patient, we are all unpaid volunteers who help in our spare time.