Sep 10
Thanks to “jaegd” for his post here: http://forums.microsoft.com/… on how to accomplish this. Below please find some more details along w/ a example which can be downloaded here (ssis-2005-excel-2007-source-example).
Steps
- Create new OLEDB Connection by right clicking on the Connection Manager tray
- Chose any valid type or value for the connection and click okay
- Rename the Connection Manager to something that makes sense, like “Excel 2007″
- Now that the the connection exists right click on it and select properties
- Edit the Connection String property IN THE PROPERTIES WINDOW to match below, updating with your Excel File Location
- Data Source=C:\MyExcelFile.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0;HDR=YES;”
New OLEDB Connection in SSIS
OLEDB Connection Manager
manually-edit-connection-string
You can now use this connection by referencing it with any task/object that would normally connect to an OLEDB source. To query it use [Sheet1$] for the table name (eg. SELECT * FROM [Sheet1$])
Do you have anything to add? Please add your comments below!
Cheers,
Ben
April 16th, 2010 at 1:25 pm
Really useful article, thanks.
One minor issue in that when I used the connection string you provided above, I got the following message:
“Could not find installable ISAM”
At the end of your conenction string, you have =YES”; when it shoudl be =YES;”
I.e. the quote and semi-colon are the wrong way around!
Otherwise, terrific. Thanks.
April 16th, 2010 at 4:52 pm
Updated…Thanks James!
May 19th, 2010 at 1:01 pm
Great article, really helped us! Did change as per the comment from James and it works. Thanks
May 26th, 2010 at 6:54 am
Great info! Thanks for posting it!
June 10th, 2010 at 3:34 pm
Thanks for the info..Could you please let me know how can I use Excel 2007 files as a Target in SSIS 2005?
June 10th, 2010 at 8:04 pm
Hi Prasanta,
I haven’t tried it but I would recommend trying to do the reverse of creating the file connection as a source. The default destination for XLS should also work for Excel 2007 users, it may just not be in the new format. Not sure what your requirements are however…
Let me know how it goes and if you run into any specific issues you might need help with.
Cheers,
Ben
June 23rd, 2010 at 10:13 pm
This does not work for me. When I enter the correct connection string I get the error message; “The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.” If I enter the original connect string I get the ISAM Error.
Here is my invalid connection string;
Data Source=C:\myfile\ProjectsLookups.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0;HDR=YES;”
If I change it to HDR=YES”; It is ‘valid’ but then I get the ISAM Error.
Anyone have any Ideas? Thanks!
June 24th, 2010 at 5:40 pm
Try moving the last double quote to separate the last two elements:
Data Source=C:\myfile\ProjectsLookups.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0″;HDR=YES;
July 9th, 2010 at 5:05 pm
I’m trying to use SSIS to get data from an Excel 2007 document into SQL server 2005 (SP3) and the connection manager won’t let me point to the Excel file in SharePoint (various test connection errors). This is a x64 bit environment running in x32 bit mode.
Import works when the Excel doc is a file. I’m using the UNC path without the “%20″ in the string.