tDbInput invalid column name error

[Anonymous]

I am trying to read data from ms sql server.

My data has the following structure:

Table Name: Service

Columns:

ServiceCode | Int

CreatedUTC | Datetime

ServiceUserId | Big Int

I am able to read ServiceCode column but whenever I want to read CreatedUTC or ServiceUserId it gives me the following error:

"Step 5a282fdf-f780-4c36-8ec4-ef91aab397a2 failed with code -1 and error Job stopped with errors or unable to run. ### Job STARTED at 2019/01/29 12:41:11 (jobId=5c5049e36dcb5435a2fb5cbf_1.1, jobExecutionId=20190129124111_Haie3) ### ### Job ENDED WITH ERROR at 2019/01/29 12:41:12 (jobId=5c5049e36dcb5435a2fb5cbf_1.1, jobExecutionId=20190129124111_Haie3) ### Exception in component tDBInput_2 (dim_class_job) java.sql.SQLException: Invalid column name 'CreatedUTC'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505) at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427) at scoot_edw.dim_class_job_0_1.dim_class_job.tDBInput_2Process(dim_class_job.java:1193) at scoot_edw.dim_class_job_0_1.dim_class_job.runJobInTOS(dim_class_job.java:2172) at scoot_edw.dim_class_job_0_1.dim_class_job.main(dim_class_job.java:2004)"

Following is the query: "SELECT Service.CreatedUTC FROM Service"

tDbInput component is infering automatically DATETIME type for CreatedUTC and is converting it to Date. Can any body guide me as to why this error is occuring?

[talend_consumer]

Can you try using tCovertType Talend component ?

[Anonymous]

Hi,

     Eventhough the tDBComponent is sending the data as date or date+time, the data is being read as a String in the outer layer. So the best way is to fetch all the records as String and then do the type conversion using tConvertType to required format.

    Option 2 is to read the data from table in a specific format (lets say DD/MM/YYYY HH24:MIS) and specify the schema in the tDBInput as Date with same format. In this way also, you can make sure that flow is working. But in this case, make sure that null values for the date column in DB are not creating any issue.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂