Tuesday, March 6, 2012

64-bit ODBC issues with DTEXEC

We've hit a weird problem that we can't resolve. We have a package using ODBC to extract from an AS400. When running the package through DTEXECUI, the package executes successfully. However, it chokes when executing through DTEXEC or as a SQL Agent job on our 64-bit machine. Seems like DTEXECUI is using the 32-bit ODBC driver while DTEXEC and SQL Agent is trying to access an non-existent 64 bit ODBC driver. We're using a DSN-less connection string (Driver={Client Access ODBC Driver (32-bit)};system=AS400;....) so that's not the issue. Anyone else seen this weird error? Any possible workarounds?If the 64-bit ODBC driver is not aviailable, you can run the package in 32-bit mode.

To do this, edit the SSIS step in Agent. Copy the text from Command Line tab. Switch step to use CmdExec subsystem instead of SSIS subsystem. Specify 32-bit DTEXEC as application ("C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" - including quotes) and add the parameters you've copied from Command Line tab.|||Thank you. This will work until we upgrade Client Access to a version supporting 64 bit drivers. This is our first 64-bit server. Was wondering why there was both "C:\Program Files\" and "C:\Program Files (x86)\" directories and didn't know how to execute dtexec in 32-bit mode.

No comments:

Post a Comment