Looking for command line versions Teradata Parallel Transporter (TD ->streaming->SQL)

• joop.terwijn

  SSC Veteran

  Points: 217

  More actions

  August 28, 2014 at 3:31 am

  #310497

  Dear colleges,

  I want to extract information form a Teradata environment and load it in SQL server. For now my process writs in a file, and then the file is load in SQLserver. Important to tell, I do not want to use SSIS but the command line version!

  So at this moment I use Teradata Parallel Transporter command line for the extraction process. Now I know it should be possible to move the data streaming with TPT from Teradata to SQLserver and so skipping the file in between. This would save time and storage on file system.

  I’m looking for examples, whom can help me with some command line version TPT scripts? For instance; I’m wondering if it’s possible to connect by trusted user to SQLserver? How to format the connection string,… etc.

  We are using; Teradata 13.10 ( Teradata Parallel Transporter, command line version )

  SQLserver 2008 R2 10.50.1600.1 (X64)

  Below an extraction of the of the spool version command line TPT I use.

  [font="Courier New"]

  DEFINE JOB CDR_TPT_VW_EXPORT

  DESCRIPTION 'TPT EXPORT VAN DE VIEW DATA DAG CDRS'

  ( DEFINE OPERATOR OPR1_TPT_EXPORT

  DESCRIPTION 'DEFINES A SPECIFIC TPT OPERATOR TO BE USED IN THE JOB OPR1_TPT_EXPORT'

  TYPE DATACONNECTOR CONSUMER

  SCHEMA *

  ATTRIBUTES

  ( VARCHAR FILENAME,

  VARCHAR FORMAT,

  VARCHAR OPENMODE,

  VARCHAR INDICATORMODE,

  VARCHAR TEXTDELIMITER );

  DEFINE SCHEMA SCH_TPT_EXPORT

  DESCRIPTION 'DEFINES THE COLUMNS AND THEIR DATA TYPES.'

  ( RECORD_GELADEN_IN_FACT VARCHAR(10),

  NETWORK_ACTIVITY_ID VARCHAR(18),

  ..

  . ( removed a lot of columns ;-) )

  ..

  CDR_BRON VARCHAR(4) );

  DEFINE OPERATOR OPR2_TPT_EXPORT

  DESCRIPTION 'DEFINES A SPECIFIC TPT OPERATOR TO BE USED IN THE JOB OPR2_TPT_EXPORT.'

  TYPE EXPORT

  SCHEMA SCH_TPT_EXPORT

  ATTRIBUTES

  ( VARCHAR USERNAME = @UsrID,

  VARCHAR USERPASSWORD = @Pwd,

  VARCHAR TDPID,

  VARCHAR SELECTSTMT,

  INTEGER BLOCKSIZE = 64330,

  INTEGER MAXSESSIONS = 126,

  INTEGER MINSESSIONS = 1,

  VARCHAR PRIVATELOGNAME = 'BXXXX_TPT_EXP_' || $JOBID || '.LOG',

  VARCHAR TRACELEVEL = 'All' );

  APPLY

  'VARCHAR QUERYBANDSESSINFO = UTILITYDATASIZE=LARGE;'

  TO OPERATOR ( OPR1_TPT_EXPORT[8]

  ATTRIBUTES

  ( FILENAME = '\\KLAN.LOCAL\SOURCES\TPT_CDRVW_DD.TXT',

  FORMAT = 'DELIMITED',

  OPENMODE = 'WRITE',

  INDICATORMODE = 'N',

  TEXTDELIMITER = '|' ) )

  SELECT * FROM OPERATOR ( OPR2_TPT_EXPORT[8]

  ATTRIBUTES

  ( SELECTSTMT = 'SELECT RECORD_GELADEN_IN_FACT,

  ..

  . ( removed also lot of columns ;-) )

  ..

  CDR_BRON

  FROM SANDBOX.VW_EXP_CDR_DD;',

  TDPID = 'database01' ) );

  );

  [/font]

  Any help is welkom!