Upfront - I'm not sure if this question belongs with this forum or another more specific to SQL Server Agent. I have a package running on Windows 2003 (64-bit) attempting to pull data from a Teradata server using its 32-bit driver. The package runs as expected within BIDS and when called from a BAT file run from the command prompt using the 32-bit version of DTEXEC. The package fails with the following error when I attempt to run it using the same *.BAT file (which succeeds from a command prompt) or when specifying the DTEXEC command directly in an operating system job step. Any insight, suggestions, or workarounds would be helpful. Executed as user: <<<userName removed>>>. ...\dtexec.exe /SQL \HRD_ODS\TheW_Connection_Test /SERVER CRPRDNMSQR2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EMicrosoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:47:59 AM Error: 2011-01-25 11:48:01.26 Code: 0xC0047062 Source: Data Flow Task DataReader Source [419] Description: System.Data.Odbc.OdbcException: ERROR [IM003] Specified driver could not be loaded due to system error 126 (Teradata) . at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConn... Process Exit Code 1. The step failed. Orlanzo

Enable the execute under 32 bit flag in the SQL Agent Job properties. Image: http://www.sql-server-performance.com/admin/article_images_new/2008_images/cannot_execute_ssis_package_files/image001.jpgArthur My Blog

Hello , please check this http://msdn.microsoft.com/en-us/library/ms141701.aspx maybe you need Edit your step and then go to Execution options and Check the Box for 32 Bit. give a try Thanks

Arthur, Aamir: I appreciate the reply guys the option is available. I should have mentioned in the original post I'm using SQL Server 2005.Orlanzo

Hi Arthur, No, the setting isn't available in SQL Server 2005. What you're referring to was introduced in SQL Server 2008.Orlanzo

You are welcome Orlanzo, curious if you have resolved the issue by applying this setting.Arthur My Blog

Add the /X86 key to the DTExecArthur My Blog

Add the /X86 key to the DTExec Arthur My Blog That option is not available with SQL Server 2005.Orlanzo

Follow this article "How to use the 64-bit SQL Server Agent to schedule a SQL Server 2005 Integration Services package": http://support.microsoft.com/kb/934653 The post walks you through on how to properly set up the Agent Job to run the 32 Bit version of DTExec.Arthur My Blog

Hi Arthur, I'm familiar with how to call the 32 bit version of DTEXEC from SQL Server Agent. I did review the article you provided and confirmed my end result is identical to what it describes.Orlanzo

Started to think this can be because your environment is clustered. I can see how this was be fixed here: http://blog.vyvojar.cz/dotnet/archive/2008/04/09/227349.aspx Short excerpt: Why: w3wp.exe didnt see path \Program Files\NCR\Common Files\Shared ICU Libraries for Teradata\lib -- in your case it might be different Fix solution: Copy files from Teradata\lib to one from directorys where execept w3wp.exe files (System32 dir, ..) So in your case running the package as SQL Server Agent does not let the file be found. It runs in BIDS because you execute the package locally (on your PC) unless you are running it directly on the server. Arthur My Blog

Orlanzo, To narrow down this issue, I recommend that you: 1. Verify what the result is if you manually execute your SSIS package with 32-bit DTEXEC (C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe) in windows commant prompt. 2. Make sure that you configure the ODBC settings via C:\Windows\SysWOW64\odbcad32.exe. 3. Open Windows Task Manager to monitor the process and check if the process name of dtexec was displayed as "dtexec*32". 4. Try uninstalling and re-installing the TeraData ODBC driver to see if it helps. http://web.datadirect.com/products/odbc/teradata/index.html, http://www.teradata.com/DownloadCenter/Forum90-1.aspx Please remember to mark the replies as answers if they help and unmark them if they provide no help

Charles, Thanks for the information. I can confirm the items you suggested have been done. I opened a case with Microsoft. Ironically, when the support professional and I weren't able to reproduce the issue. It worked as expected. The last thing I remember doing (out of many) was uninstalling all components and reinstalling using the default folders. When originally installed, I wasn't logged onto the console (RDP) and deployed to custom location. After doing so it didn't work. However, the following Monday - it worked without issue. Nothing changed on the server or the package. I'm wondering if the PATH variable hadn't been updated correctly. In any event, this issue is resolved and I do appreciate your willingness to assist.Orlanzo