Tuesday, December 11, 2012

SSIS Excel Export Job Fails on SQL 64Bit

Recently I upgraded a SQL Server from SQL 2005 32-bit to SQL 2008 64-bit.  I have a handful of SSIS jobs that export and push data to different interfacing systems.

I successfully recreated all of the jobs and ran them manually and everything worked.  I setup scheduled jobs in the SQL Server Agent to run the tasks however one job that exports data to Excel would not run successfully.  I kept getting a Jet 4.0 error and the SSIS package would not write or manipulate the excel file it was supposed to export to.

After some research I found that when a package is run manually it is run in 32-bit mode, but when the package is run in the SQL Server Agent it is run in 64-bit mode.

When running in 32-bit mode the package has access to the 32-bit ODBC drivers, but when running as 64-bit it only has access to the 64-bit ODBC drivers.  Take a look at these ODBC administrator tools to see what you have installed on your system.  (ref MS KB 942976)

  • The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
  • The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
To solve the issue make sure to run the Job Step with the package that needs access to 32-bit ODBC drivers as 32-Bit.  (See the red arrow below).  You can find this by editing SQL Server Job, selecting and editing the step that contains the SSIS package and click on the Execution Options Tab.

No comments:

Post a Comment