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
( VARCHAR FILENAME,
VARCHAR TEXTDELIMITER );
DEFINE SCHEMA SCH_TPT_EXPORT
DESCRIPTION 'DEFINES THE COLUMNS AND THEIR DATA TYPES.'
( RECORD_GELADEN_IN_FACT VARCHAR(10),
. ( 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.'
( VARCHAR USERNAME = @UsrID,
VARCHAR USERPASSWORD = @Pwd,
INTEGER BLOCKSIZE = 64330,
INTEGER MAXSESSIONS = 126,
INTEGER MINSESSIONS = 1,
VARCHAR PRIVATELOGNAME = 'BXXXX_TPT_EXP_' || $JOBID || '.LOG',
VARCHAR TRACELEVEL = 'All' );
'VARCHAR QUERYBANDSESSINFO = UTILITYDATASIZE=LARGE;'
TO OPERATOR ( OPR1_TPT_EXPORT
( FILENAME = '\KLAN.LOCAL\SOURCES\TPT_CDRVW_DD.TXT',
FORMAT = 'DELIMITED',
OPENMODE = 'WRITE',
INDICATORMODE = 'N',
TEXTDELIMITER = '|' ) )
SELECT * FROM OPERATOR ( OPR2_TPT_EXPORT
( SELECTSTMT = 'SELECT RECORD_GELADEN_IN_FACT,
. ( removed also lot of columns ;-) )
TDPID = 'database01' ) );
Any help is welkom!
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.
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.