Loading into Teradata from flat file

One Star

Loading into Teradata from flat file

Trying to get some quick staging done. I have a bunch of flat files that need to be loaded into TD. Nothing special going on.
I created objects for the delimited files and tables in Talend, then draged a delimited file to the workspace, choose tFileInputDelimited.
Then I draged my TD table to the workspace and chose tTeradataFastLoad. (I originally wanted to use the TPT option, but it wouldn't let me connect it with the delimited file, but thats another story.)
After connecting the two objects I thought I was content. There are no transformations going on, it should be an easy load operation. So I started the Job, but got an error:
Exception in component tTeradataFastLoad_1
com.teradata.jdbc.jdbc_4.util.JDBCException: Unable to set parameter index 6 because the specified target SQL type (6) does not match the SQL type (3) declared in the destination database table "DB_NAME"."TABLE_NAME"!
Any help on why the thing gives me these errors would be greatly appreciated!
One Star

Re: Loading into Teradata from flat file

Hello,
You may have an issue with the schema definition.
Double check the schema of the output table in talend and make sure it matches perfectly the one of the table in the DB.
In general (with mysql, MSSql...), when I cannot figure out where the difference is, I point the output to a new table, let talend create it for me and then compare the new table with the one I was supposed to populate.
Francesco
One Star

Re: Loading into Teradata from flat file

I let talend create the schema for the table by itself. I didn't make any modifications to what Talend created as the schema for the table.
One Star

Re: Loading into Teradata from flat file

Yes, I understand that, but you can still have an issue with that.
That's depends on exactly what you did.
I assume you added a flat file input and defined a schema.
This schema would be based on Java types, which is what Talends needs to be able to handle the data.
At the next step this java type based schema must be converted into a db specific schema and this is done using a type mapping.
However this type mapping may need sometimes some manual adjustments, i.e. to read strings from a flat file, Talend does not need to know the field length, bt when you create a table on the db this is a needed information, so you have to manually specify it.
On your db component, if you check the schema, you will see a java type definition and a sql/db type definition.
The second one is the one that might need some fine-tuning.
If this is the way you created the schema, there is a possible workaround :
First create the table in TD, then add it to the repository.
Then from the repository use it as a db output, at that point all the SQL types should be correctly defined.
Sometimes you still need to change a few things there (i.e. with "money" type fields in MSSQL)
Hope it helps.
One Star

Re: Loading into Teradata from flat file

thanks for the info!
it looks like i did exactly what you indicated in your workaround.
going to play with it some more... this thing as got to work somehow :-)
One Star

Re: Loading into Teradata from flat file

Did you try to use a teradataOutput instead of the fastload?
I mean, I guess performances would not be the same, but you may at least confirm the schema...
One Star

Re: Loading into Teradata from flat file

Works perfectly when I just connect tFileInputDelimited with tTeradataOutput.
My goal is to get this working with TPT, but I cannot find any tutorials for Talend regarding Teradata, so I'm braving this blindly :-)
Nine Stars

Re: Loading into Teradata from flat file

I was getting a similar message when I was attempting to load from a delimited file with tTeradataFastLoad, turns out the problem was the timestamp(0) column did have any data.

Moderator

Re: Loading into Teradata from flat file

Hello,

Did your job work well when connect tFileInputDelimited with tTeradataOutput?

Could you please post the full error stack trace here?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.