none
"Could not load an object because it is not available on this machine" - Error opening a 32 bit Excel 2003 spreadsheet in SSIS 2008 R2 on 64 bit machine

    Question

  • I have an SSIS package.  It runs a VB script that is trying to open spreadsheets, and read out some information from them.  This works fine for some spreadsheets, but with other spreadsheets an error occurs.  I have isolated it to those spreadsheets with a datasource defined in them.  This is a sample connection string:

    DBQ=\\poms\data\PORT Operation\PORT Operations DB.mdb;DefaultDir=\\poms\data\PORT Operation;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

    When the spreadsheet has this datasource in it (an access database), when the following command is run in the script:

    xlBook = xlApp.Workbooks.Open(FileName:=vFilename)

    Then a popup window occurs, with 'Microsoft Forms' in the title, and 'Could not load an object because it is not available on this computer'.

    The server is 64 bit, and has the 32 bit version of Excel 2007 installed, and the 32 bit version of BIDS 2008 R2, and the 32 bit version of the Access database engine installed.

    Any ideas as to what the object is that is missing?


    • Edited by magicmerl Thursday, May 26, 2011 9:41 PM typo
    Thursday, May 26, 2011 5:02 AM

Answers

  • Yes, RunTime64BitRuntime is set to false.  I'm running this from BIDS.

     

    The script is opening an Excel spreadsheet.  The excel spreadsheet contains a datasource within it (that I don't care about in this process).



    Your problem is that you do not have the appropriate Access database drivers (Microsoft.Jet.OLEDB.4.0 provider) installed so when an Excel spreadsheet is opened that needs that driver, it fails causing your package to fails. 

    Unfortunately there is no 64bit version of "Microsoft.Jet.OLEDB.4.0" provider so you need to use the "Microsoft.ACE.OLEDB.12.0" 64bit provider (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d) which may still cause you problems as the datasource in the Excel spreadsheets is using the Jet Provider.

     


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Monday, May 30, 2011 11:02 PM
  • Ok, the problem was not just the provider, it was also that there were macros in the spreadsheet as well.  It seems like trying to install and configure all of these different versions of software on a 64 bit server is in the 'too hard' basket at the moment.

    I decided to solve the problem in a different way.  Instead of using a script component I changed the data layout on the spreadsheets to make the data I wanted appear in tabular format in a new sheet (which quadruples the size of the spreadsheets).  Then I used the normal Source - Destination data flow to load those sheets into new tables in the database.  Then all of the data manipulation I was doing in the vb script component I'll do in a subsequent stored procedure in SQL Server.

    For some reason the Data Flow Component can open and read the spreadsheet without getting the error, but the VB script has the above error occur.

    Sheesh.

    Thanks for your help everybody.



    • Marked as answer by magicmerl Thursday, June 02, 2011 10:06 PM
    Thursday, June 02, 2011 10:05 PM

All replies

  • did you tried this ever? :

    in the BIDS, in the solution explorer, select the Integration service project which you work on it,

    then go to Menu->project ->properties

    in the property page,

    under debugging

    set Run64BitRuntime to false.

     

    then try again to run package from BIDS


    http://www.rad.pasfu.com
    Thursday, May 26, 2011 5:09 AM
    Moderator
  • 1) Where is the package being run? BIDS or DTExec.

    2)If in BIDS, set RunTime64bitRuntime to false and run the package.

    3)Question : Are you connecting to access or excel?


    Happy to help! Thanks. Regards and good Wishes, Deepak.
    Thursday, May 26, 2011 5:19 AM
  • Yes, RunTime64BitRuntime is set to false.  I'm running this from BIDS.

     

    The script is opening an Excel spreadsheet.  The excel spreadsheet contains a datasource within it (that I don't care about in this process).


    Thursday, May 26, 2011 8:52 PM
  • So nobody knows?

    What would be the best Excel forum I could post this on?

    Monday, May 30, 2011 10:47 PM
  • Yes, RunTime64BitRuntime is set to false.  I'm running this from BIDS.

     

    The script is opening an Excel spreadsheet.  The excel spreadsheet contains a datasource within it (that I don't care about in this process).



    Your problem is that you do not have the appropriate Access database drivers (Microsoft.Jet.OLEDB.4.0 provider) installed so when an Excel spreadsheet is opened that needs that driver, it fails causing your package to fails. 

    Unfortunately there is no 64bit version of "Microsoft.Jet.OLEDB.4.0" provider so you need to use the "Microsoft.ACE.OLEDB.12.0" 64bit provider (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d) which may still cause you problems as the datasource in the Excel spreadsheets is using the Jet Provider.

     


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Monday, May 30, 2011 11:02 PM
  • Thanks for the link.  I do have the 32 bit version of that AccessDatabaseEngine installed.

     

    The Excel instance on the server is also the 32 bit version.

    I guess I need to update the spreadsheets to use the OLEDB provider?
    Monday, May 30, 2011 11:36 PM
  • I guess I need to update the spreadsheets to use the OLEDB provider?


    That is correct
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Tuesday, May 31, 2011 12:04 AM
  • Ok, the problem was not just the provider, it was also that there were macros in the spreadsheet as well.  It seems like trying to install and configure all of these different versions of software on a 64 bit server is in the 'too hard' basket at the moment.

    I decided to solve the problem in a different way.  Instead of using a script component I changed the data layout on the spreadsheets to make the data I wanted appear in tabular format in a new sheet (which quadruples the size of the spreadsheets).  Then I used the normal Source - Destination data flow to load those sheets into new tables in the database.  Then all of the data manipulation I was doing in the vb script component I'll do in a subsequent stored procedure in SQL Server.

    For some reason the Data Flow Component can open and read the spreadsheet without getting the error, but the VB script has the above error occur.

    Sheesh.

    Thanks for your help everybody.



    • Marked as answer by magicmerl Thursday, June 02, 2011 10:06 PM
    Thursday, June 02, 2011 10:05 PM