Home > SSIS Best Practices > SSIS – Excel Connection Manager Not Supported in the 64-bit Version of SSIS

SSIS – Excel Connection Manager Not Supported in the 64-bit Version of SSIS

When you attempt to execute an SSIS package which has a Excel Destination/Source in SQL Server 64 bit (SQL Server 2008) , you will get following error.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code 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.

As error suggests, the Excel Connection Manager does not support the 64 bit version. In order to use the OLE DB for Jet providers (the Excel Connection Manager), the packages must be run in 32-bit mode. The good news is that you can still execute SSIS packages  with Excel destination/source. Depending on the environment you are running the packages, you can use different ways to run it in 32-bit mode.

1) SQL Server Agent Job – Use 32 bit runtime in SQL Server 2008

In SQL Server 2008, there should be a checkbox “Use 32 bit runtime” on the Execution options tab on the Job Step page to run the package in 32-bit mode.

2) SQL Server Agent Job – Use DTEXEC in SQL Server 2005

in SQL Server 2005, you can change the Job Step from an Integration Services Step to an Operating System step and specify a DTEXEC command line instead. Here is an example of the command you can use:

"D:\MSSQL2005\MSSQL$HEQBIP11 (x86)\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\etlCL_Send_Excel" /SERVER svrname /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V

3) In Development – Visual Studio

In development, you can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False.

4) DTEXEC Command

The following command will fail in SQL Server 2008

dtexec /f "F:\My SSIS Packages\etlCL_Import_Excel.dtsx"

To make sure that the packages is run in 32-bit mode, you must use the x86 version of dtexec and specifying the "/X86" switch. 

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /f "F:\My SSIS Packages\etlCL_Import_Excel.dtsx" /X86

Categories: SSIS Best Practices
  1. mano
    October 18, 2011 at 7:58 am

    awesome!!! it helps me without digging out so much into so called wonderfull microsoft tools…

    Like

  2. Bhanu
    September 14, 2012 at 6:21 pm

    Awesome! It worked perfect for me! Your article is pretty straight forward and easy to follow.

    Thanks!!!

    Like

  3. ejd
    April 8, 2014 at 1:20 am

    Here is the solution if you are trying to run a SSIS package with dtexec from transact SQL that contains an excel import.

    1. Create a batch file that contains the following code.

    cd C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\
    DTEXEC.exe /DE “password” /F “C:\mySSISfolder\package.dtsx”

    2. Create a short cut pointing at your batch file, set the properties\advanced on the short cut to run as an administrator.

    3. from your transact sql procedure run the following command
    exec xp_cmdshell ‘C:\myfolder\runssis_sc.lnk’

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: