Importing data from 64-bit Excel in SSIS

Last night I was #sqlhelping someone on twitter having an issue with importing data from excel. The data preview dialog returned results correctly, while the package errored at run time with a connection error. The issue was caused by Run64BitRuntime property of the package being set to true and it is in fact a well known and common issue with 64bit SSIS. Tip: install both 32bit and 64bit version of providers for your data sources if they are available.

Then it occurred to me to try it myself on my setup as I’m running 64-bit W7, 64-bit SQL stack and most importantly the first release of 64-bit Excel 2010.

The obvious path

So, I created a new package in a SSIS project and created a dataflow task. Then dragged an Excel Source from Data Flow Sources and clicked on new connection manager to define the connection to an excel file I was trying to import.

End then problems begin:

If you try to select the sheet from the dropdown (in Table or view Data access mode) the following error dialog jumps in to your face:

The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine.

Simlar but more verbose error is thrown if you try the SQL command as Data access mode with something like

select * from [Sheet1$]

It boils down to provider not being registered on the machine.

Why?

As I have already pointed out – the installed version of Excel 2010 on my machine is 64bit and BIDS being a 32-bit application can not load 64-bit stuff. The ‘Microsoft.ACE.OLEDB.12.0′ provider installed is 64-bit.

The next obvious path

So, you think, I’ll find and install the 32-bit version available from Microsoft Download (http://bit.ly/anbrtk). After downloading and running the setup for  AccessDatabaseEngine.exe you get the following error:

So it turns out you can’t have both 32-bit and 64-bit of the provider installed on the same machine.

Note that this is all due to the fact that BIDS (Visual Studio) is a 32-bit application.

So, is it hopeless?

No not at all. In fact you have two solutions to overcome this issue.

Solution 1 – using the SQL Server Import and Export Wizard

Well, this will work but it is kind of a 1 time solution because it will create an unmanageable package. But let’s go through the steps which are quite straightforward if you have used the wizard before with just a minor tweak. The most important thing is to start the  64-bit version (Import and Export Data (64-bit)).

Ok, start the Import and Export Data (64-bit) wizard and select Microsoft Office 12.0 Access Database Engine OLE DB Provider as shown on the following picture.

Now click on Properties and set the path to your excel file in the Data Source text box:

Clicking on the Test Connection button yields the following error dialog.

This is the minor tweak needed that I mentioned earlier. Switch to All tab in the Data Link Properties dialog and double click on Extended Properties.

Insert “Excel 12.0″ in the Property Value (without quotes) and click OK.

Return to the Connection tab and click Test Connection button. It should suceed now.

Next set the destination. Here I have set it to database TEST on my local named instance of SQL server (localhost\SQL2008).

After clicking next, choose from the options. Lets just leave it at the default for now and click next.

Place a check for the sheet that contains data and either leave the default destination on choose from existing tables.

We’ll skip the mappings part for now and just click Preview to test.

In the Save and Run Package step  of the wizard leave checked Run immediately and check Save SSIS Package with the File system destination option and click next.

I had trouble naming the package so just leave it with default values (New Package.dtsx in My Documents) and click next.

A summary of settings is displayed. Click Finish.

Remember we selected the option to run the package immediately, so the package is started and we get the following screen.

You can test it from SSMS.

Now let’s open the package in Visual Studio 2008 or BIDS.  Click on the Error List pane and note the errors.

You will not be able to work with this dataflow in designer. And that leads us to an alternate solution.

Solution 2 – Using OLE DB Source in DataFlow with SQL Server as the Data Source

This solution will result in a maintainable and editable package that has no dependency on the development platform. It will work even if you do not have the ‘Microsoft.ACE.OLEDB.12.0′ provider installed as long as the provider is installed on any remote machine running SQL server 64-bit provided you have access to it.

As this solution uses OPENROWSET to provide data from Excel we have to setup SQL server options.

Fire up SSMS and execute the following script on the instance of sql server 64-bit that you will be using. I was using my local instance of SQL Server 2008 Developer Edition 64-bit. If you are using anything other than test/dev server consult your system admin first.

-- Code start
USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' ,  N'AllowInProcess' ,  1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' ,  N'DynamicParameters' ,  1
GO
-- code end

Now test the configuration with a query similar to this (adjust the excel path for your environment):

-- test query 1: change the path to your excel file
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=C:\twitter\sqlcheesecake.xlsx'
, [Sheet1$]
)

 

-- test query 2: change the path to your excel file
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=C:\twitter\sqlcheesecake.xlsx'
, 'select top 2 * from [Sheet1$]'
)

If the tests pass and results are returned we can move on.

Fire up BIDS or Visual Studio and create a new SSIS Project. Drag a data flow task in the control flow and double click on it to open the Data Flow tab.

Now drag an OLE DB Source component from the Data Flow Sources and double click to configure it. Create the connection manager to the SQL server that you will be using. Set the database to the database that you will be importing the excel data (this is not required but I have used the same connection for the OLE DB destination to keep it simple and stupid).

From the Data access mode dropdown select SQL command (SQL command from variable is also an option but it is not important for the concept).

Type in (paste) your test query in the SQL command text box.

Now click on the Preview button to test your query. It should display results if everything is setup ok.

Now drag an OLE DB destination to the data flow, connect the source component to the destination and configure your destination connection  and  table. I’m reusing the same connection manager.

Configure column mappings and you are ready to go.

Right click somewhere in the data flow and select Execute Task to run the data flow task.

If everything is ok  you should see the beautiful green boxes all around :)

Well, this turned longer to write than I expected but I know I have saved a lot of time to a lot of peeps in the Community.

Cheers,

Hrvoje Piasevoli

@HrvojePiasevoli

  • Pingback: Tweets that mention Importing data from 64-bit Excel in SSIS | Hrvoje Piasevoli – BI Blog -- Topsy.com

  • Anovick

    I’ve tried the procedure using the 64-bit Import/Export Wizard with no success.
    On a fresh SQL 2008 R2 install (Windows 2008 R2 x64) I’ve installed the Office 2010 64 bit ACE.
    I follow the procedure and I get as far as testing the connection, which succeeds. Once I press Next, the program terminates and Windows Server tells me that it has.

    • Simple Simon

      Just open the xlsx in office 2010 and save as xls, then import from BIDS as always.

  • Anovick

    I didn’t quite get to complete that post. But the jist is there. It crashes. Is there an earlier version of ACE that I should be using?
    Thanks
    Andy
    Andrew Novick
    SQL Server MVP
    http://www.NovickSoftware.com

  • http://hrvoje.piasevoli.com Hrvoje Piasevoli

    Hi Andrew,
    I’ve just tested the Import/Export Wizard on SQL 2008 R2 (64 bit) + W2k8 R2 with ACE 64 bit and works without problems. Note that I also have Excel 2010 installed on that server. Can I contact you over e-mail/skype/gtalk? My account is hrvoje.piasevoli.

    • http://hrvoje.piasevoli.com Hrvoje Piasevoli

      Hi Andrew,
      I just want to update with my latest testing. After having uninstalled Excel from the server, removing Desktop experience feature and installing the Office ACE 64bit provider I can confirm that Import/Export Wizard on SQL 2008 R2 (64 bit) + W2k8 R2 with ACE 64 bit works without problems.

      Regards,
      Hrvoje

  • techvslife

    The first solution does not allow you to edit the package, which many of us need to add things like unpivots and other transforms.
    The second solution does not work for access accdb databases (there’s a bug — only excel spreadsheets can be linked to by sql server using the 64-bit drivers):
    https://connect.microsoft.com/SQLServer/feedback/details/587897/connecting-via-a-linked-server-to-an-access-2010-database-file

  • Pingback: So You’ve Installed SQL Server…Now What? « Learning SQL Server

  • Ahmad_hashemi

    I have problem with Solution 2 – Using OLE DB Source in DataFlow with SQL Server as the Data Source
    i got: OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Unspecified error”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

    • http://hrvoje.piasevoli.com Hrvoje Piasevoli

      Hi, Have you set the server properties (first code snippet)? Do you have the Office ACE 64bit provider installed on your SQL server?

      • Alexander Schneider

        Hi, I set the server properties from the first code snippet and my admin installed “Microsoft Access Database Engine 2010 Redistributable” 64bit and I have still the error Ahmad_hashemi described.
        Any idea, what could be the problem?

  • Jopicarra

    I’m having a problem using the solution 2 but in a Store Procedure.
    Sometimes, if I try to load many excel files, the transaction never ends, and when you cancel, it stays rolling back for all eternity, or until you reset the SQL server. Did it ever happened to you using SSIS?
    Maybe it’s time to change to SSIS.

    • http://hrvoje.piasevoli.com Hrvoje Piasevoli

      Hi, no I haven’t done any excessive testing at all unfortunately.

  • Richard Mascarenhas

    What a wonderfully written post… nice job and yes, you saved me a LOT of time and hassle. Thank you so much!

    • http://hrvoje.piasevoli.com Hrvoje Piasevoli

      Hi Richard, so nice to here that. Thanks for dropping a comment other than “…it’s not working for me” :). What I didn’t realize when I was writing the post was that it’d also save my time more than once. Cheers

  • Ken Woodruff

    FYI, I was able to get this working using the ACE OLEDB driver directly as an OLE DB Source in a dataflow without needing to go through SQL Server or OPENROWSET.  Details here http://www.codesuck.com/2011/07/reading-excel-files-into-ssis-on-64-bit.html

    • http://hrvoje.piasevoli.com Hrvoje Piasevoli

      Hi Ken! I’m sorry to say that you have missed to understand the problem described. What I am describing in my post is how to solve the problem of importing data from Excel (or be it any other data source) that has ONLY 64-bit drivers installed and the 32-bit drivers are not available or as in the case of ACE OLEDB drivers – can’t be installed side-by-side (see the 4th screenshot from top). So, the OS bitness is not relevant here – it is the unavailability of 32-bit data source provider, for all the reasons explained at the beginning of the post.So I suggest you update your post to make this a bit clearer. Thanks!

      • Ken Woodruff

        OS bitness is indirectly relevant in that it affects the availability of WOW64 for BIDS and which flavor of dtexec runs by default.  Even though it’s a 32-bit app BIDS should be able to use the (already installed in your scenario) 64-bit ACE OLEDB driver, but only if you use an OLE DB source (and connection manager) instead of an Excel source.

        • Ken Woodruff

          Correction to this–you can use the already installed 64-bit driver from dtexec, but possibly not BIDS itself.

  • http://pulse.yahoo.com/_AUAFU263ME5K4LVOEOZPKZLYQE Jeff

    People like Hrvoje make insurmountable tasks simple with easy to follow instructions. Excellent job!! Worked like a champ. Thank you for taking the time to teach us.

  • Pingback: Making SSIS work with the 64-bit Excel (ACE) provider

  • Vinuthan Bhat

    Nice post buddy

  • MarkGStacey

    Hi

    Easy solution is to run Office 2007 32 bit *after* the 64 bit install, as it doesn’t do the checks

    • http://hrvoje.piasevoli.com Hrvoje Piasevoli

      Hi Mark,
      I didn’t know that. However, I am not sure I would be comfortable doing or recommending that as it doesn’t sound like a supported scenario.

  • Colin Macguire

    Hi
    Hrvoje. I spent the past 45 minutes Googling different ways of getting an XLSX
    file into SQL via SSIS on a 64bit system with the 64bit Access 2010 drivers
    installed, and pieced together the solution one bit of code at a time. After I
    felt very good about myself for solving the problem I came across this blog
    post which made me make the sounds Homer Simpson makes when he’s done something
    stupid or when he just realized something… DOPE! In South Africa we have a similar
    sound to Homer’s “Dope”, and it’s called “Eish”!

     

    So a HUGE Eish to myself
    for not finding this wonderfully written article 45 minutes ago :/ I guess the
    upside is I have learnt something new, which is always a good thing! Keep up the good work!

  • Basil_Nolan

    Hrvoje, you’re a god.

    As many here, I was nearly crumbling under the pressure of getting some Excel tables gone to SQL, and the bloody driver just won’t play with my x64 MSSQLServer instance. But your method number 2 did yield amazing results. And I’m thankful for that.

    However, I cut short the method by using a shortcut. Since I only needed some specific tables frommy xlsx in the database, once I managed to query the file I just did “INSERT INTO table SELECT * FROM OPENROWSET…” and it worked like a charm.

    Thanks for this very handful solution! (^_^)

  • Kskistad

    One thing I discovered that may help someone else is the error “DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available” is somewhat misleading, especially the last part saying no provider is available when you know it is there and can verify it in a number of ways that it is installed correctly.  I think the real issue is the Excel Source is not 64-bit compatible.  So instead of using that as a source adapter, use a regular OLEDB Connection Manager and Source, and specify the Extended Properties “Excel 12.0;HDR=YES;” for Excel 2007/2010 files (.xlsx) and “Excel 8.0;HDR=YES;” for lower versions (.xls).

    I sometimes get the error “Can’t initialize provider” or something to that effect when testing the connection manager or setting up the source, but found you have to close and reopen BIDS to get the connection to succeed.

    If you have the 32-bit Microsoft.ACE.OLEDB.12.0 provider installed on your developer workstation and the 64-bit version installed on the server that hosts SSIS, a SQL Agent job will run successfully in both environments without turning on the “Use 32 bit runtime” in the job step.  Also, as others have said before, you can install both the 32-bit and 64-bit providers together as long as you install the 32-bit drivers first (i.e. if you use BIDS in a 64-bit environment).

    • Vladimir

      Many thanks! Works great.

  • rory

    Fantastic!! you have no idea how much anguish and despair has been removed from me!! Was trying to do exactly this for the best part of this week!!
    All i need to do now is figure out how to apply  this for loading multiple excel files, is it possible to dynamically alter the name of the file that the SQL command text uses?
    Anyway thanks agaain :-)

    • http://hrvoje.piasevoli.com Hrvoje Piasevoli

      Thanks. Yes it is possible to make the sql command dynamic – for example in a foreach loop using a variable.

  • Fabio Landi

    This is the best post on this topic ever!!!!!
    Thanks you so much!

  • Pingback: SSIS – Connecting to Access 97 or Excel 2010 «POP-BI POP-BI

  • K Reid

    Post worked perfectly, thanks for the great advice.

  • rajeev

    This worked like a song… Thanks a ton.

    there is one problem though. After changing the settings using sp_configure, this works fine for administrator, but not for other users.
    how does one set it up for the other users?
    Rgds,
    Rajeev.

  • rajeev

    sorry left out the error message:

    “Ad hoc access to OLE DB Provider “Microsoft ACE.OLEDB.12.0″ has been denied. You must access this provider through a linked server”.
    component “OLE DB Source(14)” failed validation and returned status “VS_ISBROKEN”.

  • rajeev

    This worked like a song… Thanks a ton.

    there is one problem though. After changing the settings using sp_configure, this works fine for administrator, but not for other users.
    The error got is:
    “Ad hoc access to OLE DB Provider “Microsoft ACE.OLEDB.12.0″ has been denied. You must access this provider through a linked server”.
    component “OLE DB Source(14)” failed validation and returned status “VS_ISBROKEN”.
    how does one set it up for the other users?

    Rgds,

  • Nathon Dalton

    BRILLIANT! As I’m sure many have said… Thanks!

  • Pingback: Gregory Smith

  • Shashak

    This is awsm……..This works for me. :)

  • enscorps

    Second solution has worked for me