SSIS with Excel Source/Destination on x64 SQL Server Instance

Not all people know, that the Excel file format is not supported by the x64 version of SQL server and the same apply to the SSIS runtime. This apply not only to excel but to all data sources for which you have only 32 bit drivers (e.g. ODBC drivers of external data sources etc.

Although you can develop the SSIS package with Excel source/destination in Business Intelligence Studio (BIDS)  on x64 machine, you can design the whole ETL process, but when you try to debug such package, you will receive an error message, that Excel file source is not supported on x64.

Debugging on x64 machine

If you want to debug a package on x64 machine, you have to set the environment not to use the x64 runtime. BIDS by default uses the x64 runtime on x64 machines so it is necessary to disable it. You can do this in the SSIS project properties.

SSIS Project Properties in BIDS

Once you set the Run64BitRuntime to false, you will be able to run and debug the package in the BIDS environment.

Running package on x64 version of SQL Server

As Mentioned above, you can set the SSIS properties, so the BIDS does not use the x64 runtime for running the package containing an Excel source/destination. Unfortunately this settings is only for design time debugging in the BIDS environment. When you deploy the package to the server and try to run it directly or from within and agent job, execution will fail as the Excel is not supported by x64 version of SQL Server.

SQL Server 2008 and newer

On the SQL Server 2008 and newer the solution is easy as the Job Step Properties dialog for a SQL Server Integration Services Package type has an option to execute the package using the 32 bit runtime. The option can be found on the Execution options tab. Once you set the “Use 32 bit runtime”, the SSIS package will run correctly on the x64 version of SQL Server 2008 and above. The option simply set the /X86 argument of the DTExec utility and you can see it on the Command Line tab, once you check it.

Agent Job Step Properties

SQL Server 2005

Situation is quite different on SQL Server 2005 as the Job Step Properties dialog doesn’t have the option to run the package using the X86 runtime. Although it seems, that we are blocked and it will not be possible to run such SSIS package on the x64 version of SQL Server 2005, there is one workaround you can use to run that package. The workaround is to use Operating System (CmdExec) task and use the x86 version of DTExec utility which is also installed on the x64 instance of SQL Server. It’s located in the SQL Server installation directory under “Program Files (X86)” in DTSBinn directory.

To simplify configuration, first choose the SQL Server Integration Services Package Type and select location a SSIS package you want to run.

New Job Step

Once you have selected the package and configured everything necessary for the package execution, you can switch to the Command Line tab where you can see all the necessary arguments to execute the DTExec utility. If you know all the arguments, you do not need to do this steps and simply configure the CmdExec task, although in this way you do not need to write the arguments manually.

New Job Step Command Line

After setting all the necessary parameters, change the step Type to Operating System (CmdExec). All the arguments for the DTExec appear in the command TextBox.

New Job Step - CmdExec

Now you only add the x86 version of DTExec utility including whole path to it.

New Job Step - CmdExec after update

After this, you have a SSIS Package with Excel source/destination (or other data source/destination with only 32bit drivers) configured to run on X64 instance of SQL Server.

Denali side by side installation with SQL 2008 or SQL 2008 R2 corrupts BIDS

Today I’ve just installed a SQL Server Code Name Denali CTP1 as a named instance side by side with instance of SQL Server 2008 R2 to take a look on the new features of Denali

Everything worked fine, unless I started a BIDS and wanted to create a new SSIS project (or open existing one).

Once you try it, you will se a below message.

Corrupted Installation of BIDS cannot open project

The problem is that the CTP1 version of Denali still uses VS2008 shell as Business Intelligence Development studio. In future versions it will be replaced by the VS2010 shell. But now it uses the V2008 shell as SQL Server 2008 or 2008R2 and the Side by Side installation corrupts BIDS.

To correct this, you have to uninstall Denali, and reinstall SQL2008. Then you can install Denali without BIDS.

So to save you trouble, and you want to install Denali side by side with existing SQL 2008 or SQL 2008 R2 installation, you can install all the features of Denali, except the BIDS.