When reading datetime2 fields with tMSSqlInput, all the date values come as null.
I even specify the date pattern "yyyy-MM-dd HH:mm:ss.SSS" in the schema definition, but to no avail.
I have also tried the tMSSqlRow component and it produces the same results.
I would appreciate your help in fixing this issue.
Solved! Go to Solution.
Yes i'm using the Microsoft jdbc driver as required per the MSSqlInput component. There is data in the table that I'm querying, but it simply comes as null.
Another odd, perhaps unrelated, behaviour, is that when I press guess schema based on the query, Talend produces the following error "class not found net.sourceforge.jtds.jdbc.driver". Is there something wrong with the installation?
Edit: Using a jtds jdbc connection to the MsSqlServer fixes the datetime related issue.
Changing JDBC provider from Microsoft to open source JTDS as suggested by @Ram was fix for the titled issue, few days back, when I was running the jobs in development environment.
Now, I m running the same jobs on staging environment which giving an error related to connection : "Invalid state : Connection object is closed". Further, I tried changing JDBC provider to Microsoft, the issue of connection has resolved. But again the null date issue has raised with JDBC provider as Microsoft.
In my case Microsoft JDBC retuning Null for Date field, but its perfect with DateTime field.
So, I just changed tMSSqlInput component query for Date field as :
SELECT CAST(Date_Field as Datetime) from Table_Name
instead of :
SELECT Date_Field from Table_Name
I don't know if this is efficient solution or not, but if the issue is driver related then one can go for the above solution.
Hope this would help someone facing the same issue with mssql-jdbc.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema
Pick up some tips and tricks with Context Variables