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

Tools

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

Dear colleges,

I want to extract information form a Teradata environment and load it in SQL server ( I know this is not done :-P ). 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 SQLserver SSIS but TPT command line version!

So at this moment I use Teradata Parallel Transporter command line for the extraction process. Now I believe 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.

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' ) );
);

Any help is welkom!

Tags (3)
2 REPLIES
Senior Apprentice

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

Hi Joop,

I never did this, but you should be able to use a DataConnector as consumer with an OLEDB Access Module.

Check the Job Example 13: Export Data and Process It with an Access Module in the TPT User Guide manual.

Details on how to use OLEDB Access Module can be found in the Access Module Reference manual. 

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

Dieter,

Thx for the hint. I will investigate J Plowing through the internet and several forums I did not find any example. Usually somebody else has faced your problem before you (and solved is). In this case I draw a blank… it must be difficult or unconventional.

Joop