none
Package Fails When Using 32-bit Teradata Driver

    Question

  • 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
    • Edited by Orlanzo Tuesday, January 25, 2011 7:38 PM removed user name
    Tuesday, January 25, 2011 6:38 PM

Answers

  • 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
    • Marked as answer by Orlanzo Wednesday, February 02, 2011 4:50 PM
    Wednesday, February 02, 2011 4:50 PM

All replies

  • Tuesday, January 25, 2011 6:54 PM
    Moderator
  • 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

    Tuesday, January 25, 2011 6:57 PM
  • 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
    Tuesday, January 25, 2011 7:37 PM
  • You are welcome Orlanzo,

    curious if you have resolved the issue by applying this setting.


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, January 25, 2011 7:38 PM
    Moderator
  • Hi Arthur,

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


    Orlanzo
    Tuesday, January 25, 2011 7:42 PM
  • Add the /X86 key to the DTExec
    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, January 25, 2011 7:56 PM
    Moderator
  • Add the /X86 key to the DTExec
    Arthur My Blog
    By: TwitterButtons.com
    That option is not available with SQL Server 2005.

    Orlanzo
    Tuesday, January 25, 2011 8:05 PM
  • 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
    By: TwitterButtons.com
    Tuesday, January 25, 2011 8:10 PM
    Moderator
  • 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
    Tuesday, January 25, 2011 8:38 PM
  • 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
    By: TwitterButtons.com
    Tuesday, January 25, 2011 9:05 PM
    Moderator
  • 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
    Tuesday, February 01, 2011 6:59 AM
    Moderator
  • 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
    • Marked as answer by Orlanzo Wednesday, February 02, 2011 4:50 PM
    Wednesday, February 02, 2011 4:50 PM