SSIS 2005 Import Excel (xlsx) file

  • 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!

  • 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?

  • 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

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

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

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

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