July 29, 2010 at 8:50 am
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!
July 29, 2010 at 11:54 am
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?
July 29, 2010 at 11:54 am
November 27, 2012 at 12:11 pm
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
November 30, 2012 at 4:01 am
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...
November 30, 2012 at 4:34 am
raghavender.2369 (11/30/2012)
col1 col2 col35.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