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
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.
Updated…Thanks James!
Great article, really helped us! Did change as per the comment from James and it works. Thanks
Great info! Thanks for posting it!
Thanks for the info..Could you please let me know how can I use Excel 2007 files as a Target in SSIS 2005?
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
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!
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;
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.
Thanks, this works for me except for one thing. I have one column in my xlsx file that exceeds 256 characters. It seems as though this method still confides by the Excel 2003 rules for column size. It’s getting truncated
Anyone else run into this?
how to pass file path as a veriable.
This article help me solve a problem with “Could not find installable ISAM” with excel 2007 file on MS SQL ( xlsx ). Thanks. OpenRowset function generate the same error when ; is missed
Does this work on a 32 bit SQL Server 2005 and client?
Does this work on a 32 bit SQL Server 2005 and client?
Hi I tried as described but on running query “select * from [sheet1$] does not work and generate this error.”Multiple-Step OLE DB operation generated errors. Check each OLE.DB operation” Error code 0xC0202009. Any help will be appreciated.
I am also face the problem of ISAM error
Thanks for the post, but the solution simply isn’t correct; why not check to confirm connection string doesn’t lack errors before posting it? I did, with some advice above, get the correct string, but also getting the ISAM error.
I really wanted to write down a quick remark in order to appreciate you for all of the fabulous facts you are giving out at this website. My time intensive internet search has at the end been compensated with high-quality suggestions to write about with my neighbours. I would say that we site visitors are rather endowed to dwell in a magnificent place with so many wonderful professionals with great secrets. I feel truly grateful to have encountered the website and look forward to tons of more brilliant moments reading here. Thank you once again for everything.
I’d need to check with you here. Which just isn’t something I generally do! I delight in reading a post that may make persons think. Also, thanks for allowing me to comment!