fbpx

Using Excel 2007 files as a Source in SSIS 2005

Posted on

Thanks to “jaegd” for his post here: 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

  1. Create new OLEDB Connection by right clicking on the Connection Manager tray
    2. New OLEDB Connection in SSIS
    New OLEDB Connection in SSIS
  2. Chose any valid type or value for the connection and click okay
    3. OLEDB Connection Manager
    OLEDB Connection Manager
  3. Rename the Connection Manager to something that makes sense, like “Excel 2007”
  4. Now that the the connection exists right click on it and select properties
  5. 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;”

    6. manually-edit-connection-string
    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

, ,

24 Comments

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

  3. Great article, really helped us! Did change as per the comment from James and it works. Thanks

  5. Thanks for the info..Could you please let me know how can I use Excel 2007 files as a Target in SSIS 2005?

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

  7. 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:myfileProjectsLookups.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!

  8. Try moving the last double quote to separate the last two elements:

    Data Source=C:myfileProjectsLookups.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0″;HDR=YES;

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

  10. 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?

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

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

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

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

  19. 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!

  20. Thanks for your post.I did this above process and i am successful in generating excel file in xlsx format but when i tried to open it it is showing error that file format or extension is not valid.

    is there any solution to this problem.

  21. Hi,

    I am trying to import data from excel file into SQL Server using SSIS. One of the column in excel file having most of the date values and some character values and Null values .when i am trying to import string valus come as Null values.

    I have already modified connection string by appending “IMEX=1” for Excel Connection, but still not getting correct data imported to SQL.

    Can anybody throw some light why “IMEX=1” not working for me ? I dont want to update registry value and also dont want to modify excel file as it would be daily feed from excel to sql.

    Thanks

  22. Can i Insert string values in datetime filed .if it is right how can i convert
    please help me on that

  23. Pingback: SSIS – Saving to Office 2007 Format | Zerr

Comments are closed.