Errors connecting to SQL Server source using TPT

Tools

Errors connecting to SQL Server source using TPT

We are trying our first job with TPT and are having problems connecting to our source database.  We are receiving this error...

TPT17101: Fatal error received from ODBC driver:

STATE=IM002, CODE=0,

MSG='[DataDirect][ODBC lib] Data source name not found and no default driver specified'

I am including copies of the files in use as well as a copy of the output.

-----------------UNIX Shell

#!/bin/ksh

#set -x

LOGONDIR=/etl/ST/ABC/DEF/LOGON

. $LOGONDIR/DEF_DB.sh

echo SQL_ABC_ODBC_SRC_DB $SQL_ABC_ODBC_SRC_DB

ODBC_DSNName=$SQL_ABC_ODBC_SRC_DB

echo ODBC_DSNName $ODBC_DSNName

TERADATA_HOME=/usr/odbc

TD_TPT=/opt/teradata/client/14.10

TD_TPT_HOME=$TD_TPT/tbuild

TD_ICU_DATA=/opt/teradata/client/14.10/tdicu/lib

TWB_ROOT=/opt/teradata/client/14.10/tbuild

TD_HOME=$TERADATA_HOME

ODBCHOME=/opt/teradata/client/ODBC_32

export ODBCINST=/etl/ST/ABC/LOGON/odbcinst.ini

export ODBCINI=/etl/ST/ABC/LOGON/odbc.ini

export TD_TPT ODBCHOME ODBCINST ODBCINI TERADATA_HOME TD_TPT_HOME TD_ICU_DATA TWB_ROOT

PATH=$TD_TPT:$TERADATA_HOME/bin:$TD_TPT_HOME/bin:$TD_ICU_DATA:$TWB_ROOT/lib:/usr/bin:/etc:/usr/java5/bin:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:$ODBCHOME/bin:$ODBCHOME/lib:$TERADATA_HOME/bin:/opt/quest/bin:$MY_HOME:.

LIBPATH=$TD_TPT:$TERADATA_HOME/lib:$TD_TPT_HOME/lib:$TD_ICU_DATA:$TWB_ROOT/lib:/usr/lib/lib:$ODBCHOME/bin:$ODBCHOME/lib:$TERADATA_HOME/lib:/usr/opt/$DB2HOME/lib64:.

export PATH LIBPATH

tbuild -f /HOME/ABC/drh7742/TPTScripts/DRH_GUIDE_ABC.tpt -j DRH_GUIDE_ABC

---------------------------TPT Script

/***************************************************************************************************/

DEFINE JOB ODBC_LOAD

(

DEFINE SCHEMA qa_result

  (

  uname VARCHAR(50));

DEFINE OPERATOR ABC_operator

   TYPE ODBC

   SCHEMA qa_result

   ATTRIBUTES

   (

     VARCHAR UserName = 'myuser', 

     VARCHAR UserPassword = 'mypassword',

     VARCHAR DSNName = '@ODBC_DSNName',

   VARCHAR SelectStmt  = 'Select uname FROM mydb.dbo.mytable where uid =601039843;',

   VARCHAR PrivateLogName = 'PrivateQuality_Audit_Result_log'

   ); 

DEFINE OPERATOR STREAM_operator

   TYPE STREAM

   SCHEMA *

   ATTRIBUTES

   (

  VARCHAR TdpId = 'my_td_db_id',

    VARCHAR UserName = 'my_td_User',

    VARCHAR UserPassword = 'my_td_Password',

    VARCHAR LogTable = 'my_td_db.Result_log',

  VARCHAR WorkingDatabase  = 'my_td_db',

    VARCHAR TargetTable = 'my_td_db.drhTPT'

   ); 

 STEP step1

(

  APPLY

   ('INSERT INTO my_td_db.drhTPT

   (columnone)

   VALUES ( :uname);')

    TO OPERATOR (STREAM_operator)

   SELECT uname FROM OPERATOR (ABC_operator);

      );

);

-------------------DEF_DB.sh

#  @@START EXPORTED_VARIABLES

export SQL_ABC_ODBC_SRC_DB='SQL_ABC'

export SQL_ABC_ODBC_SRC_USER='myuser'

export SQL_ABC_ODBC_SRC_PASSWORD='mypassword'

#  @@END EXPORTED_VARIABLES

#  End of Exported variables section



-------------------------------------------------------

--------------------------------ODBC ini

[ODBC]

IANAAppCodePage=4

InstallDir=/opt/Progress/DataDirect/Connect64_for_ODBC_61

Trace=Yes

TraceFile=/etl/admin/odbctrace.out

TraceDll=/opt/Progress/DataDirect/Connect64_for_ODBC_61/lib/ddtrc25.so

[ODBC Data Sources]

SQL_ABC=DataDirect 6.1 SQL Server Wire Protocol

[SQL_ABC]

Driver=/etl/usr/dmexpress/ThirdParty/DataDirect/lib/_Ssqls26.so

Description=SQL Server

Databasee=mydb

Address=myserver, 98765

LogonID=

Password=

QuoteId=NO

AnsiNPW=No

---------------------------------------------------

--------------Output

SQL_ABC_ODBC_SRC_DB SQL_ABC

ODBC_DSNName SQL_ABC

Teradata Parallel Transporter Version 14.10.00.02

Job log: /opt/teradata/client/14.10/tbuild/logs/DRH_GUIDE_ABC-297.out

Job id is DRH_GUIDE_ABC-297, running on n#####11

Found CheckPoint file: /opt/teradata/client/14.10/tbuild/checkpoint/DRH_GUIDE_ABCLVCP

This is a restart job; it restarts at step step1.

Teradata Parallel Transporter Stream Operator Version 14.10.00.02

STREAM_operator: private log not specified

Teradata Parallel Transporter ODBC Operator Version 14.10.00.02

ABC_operator: private log specified: PrivateQuality_Audit_Result_log

ABC_operator: connecting sessions

ABC_operator: TPT17122: Error: unable to connect to data source

ABC_operator: TPT17101: Fatal error received from ODBC driver:

              STATE=IM002, CODE=0,

              MSG='[DataDirect][ODBC lib] Data source name not found and no default driver specified'

ABC_operator: disconnecting sessions

ABC_operator: TPT17124: Error: unable to disconnect from data source

ABC_operator: TPT17101: Fatal error received from ODBC driver:

              STATE=08003, CODE=0,

              MSG='[DataDirect][ODBC lib] Connection not open'

ABC_operator: Total processor time used = '0.006269 Second(s)'

ABC_operator: Start : Sun Dec  7 15:33:34 2014

ABC_operator: End   : Sun Dec  7 15:33:34 2014

STREAM_operator: Start-up Rate: UNLIMITED statements per Minute

STREAM_operator: Operator Command ID for External Command Interface: STREAM_operator31064610

STREAM_operator: connecting sessions

STREAM_operator: disconnecting sessions

STREAM_operator: Total processor time used = '0.321904 Second(s)'

STREAM_operator: Start : Sun Dec  7 15:33:34 2014

STREAM_operator: End   : Sun Dec  7 15:33:40 2014

Job step step1 terminated (status 12)

Job DRH_GUIDE_ABC terminated (status 12)

Job start: Sun Dec  7 15:33:30 2014

Job end:   Sun Dec  7 15:33:40 2014

Tags (3)