topic Re: PLEASE HELP! MS SQL Server varchar change to varbinary in Talend Studio

PLEASE HELP! MS SQL Server varchar change to varbinary

Anonymous — Sat, 16 Nov 2024 13:19:27 GMT

Hi,
I had a simple job trying to import data from excel into MS SQL Server.
tFileExcelInput -> Main -> tMap -> Main -> tDBOutput
In the tDBOutput, I connected to MS SQL Server which has column datatype defined as "varchar"; however, the schema in Talend changes to "varbinary" datatype and I cannot import the data into MS SQL Server unless I use datatype "byte[]" in Talend which after the job is executed the data is imported as NULL.
Can you anyone please help!!
Thanks in advance!

Re: PLEASE HELP! MS SQL Server varchar change to varbinary

Anonymous — Wed, 04 Aug 2010 22:55:25 GMT

Hi Klmc,
Why did you use the tDBOutput instead of the tMSSQLOutput ?
I assume you prefer to use the ODBC driver than the JDBC driver, correct ?
In Talend; the Mapping between DataType from the Database and TalendType can be customize to fit and match with your need.
In your Studio, you can open the Preferences (go to the Menu, choose Window and then Preferences ); dig into Talend > Specific settings > Metadata of Talend.
You'll find the metadata Mapping to resolve DataType to TalendType and vice versa.
For tDBOutput, the metadata file should be mapping_MsOdbc.xml, you can edit it and then save it. It would be better to convert your VARCHAR to String, or your String to VARCHAR.
By the way, I have check and maybe you have done a quick mistake in your Schema definition because I'm not able to reproduce your issue.
What is the version of the studio you use ?
Hope that helps in your resolution.

Re: PLEASE HELP! MS SQL Server varchar change to varbinary

Anonymous — Thu, 05 Aug 2010 00:05:09 GMT

Hi Cantonie,
Thanks for your quick reply!
I used tDBOutput because I prefer to use ODBC driver and the TOS version is 4.0. I am not sure what kind of mistake I did in my schema definition. I used the functionality "Retrieve Schema" in Metadata and define the repository in tDBOutput and TOS automatically coverts "varchar" to "varbinary" which when I want to edit schema, I cannot change "varbinary".
If possible, can you please give me an example on how to modify the mapping_MsODBC.xml? If I want VARCHAR to String or String to VARCHAR.
Thanks again and hope to hearing from you soon.

Re: PLEASE HELP! MS SQL Server varchar change to varbinary

Anonymous — Thu, 05 Aug 2010 08:43:40 GMT

Hi,
I tried couple of ways to fix the issue but none of them work, any help is really really appreciated.
I encountered error:
==> Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.
1) Used tMSSQLOutput and use Built-in Schema option and built the schema to use 'String' datatype which converts to
2) Changed the mapping_mssql.xml as follows:
<dbType type="VARBINARY">
<talendType type="id_String" default="true" />
</dbType>
<dbType type="VARCHAR">
<talendType type="id_String" default="true" />
</dbType>
<dbType type="NVARCHAR">
<talendType type="id_String" default="true" />
</dbType>
<talendType type="id_String">
<dbType type="VARCHAR" default="true" />
<dbType type="NVARCHAR" />
<dbType type="NCHAR" />
<dbType type="NTEXT" />
<dbType type="TEXT" />
<talendType>
Thanks in advance!

Re: PLEASE HELP! MS SQL Server varchar change to varbinary

Anonymous — Wed, 11 Aug 2010 20:08:23 GMT

Does anyone has the same issue? Any help / idea is really really appreciated!

Re: PLEASE HELP! MS SQL Server varchar change to varbinary

Anonymous — Wed, 11 Aug 2010 21:57:30 GMT

hi klmc
i have mainly used Talend with MSSQL and i have to say that i have not found your problem as yet.
as pointed out by 'cantoine' you would find more efficient and adequate to use MSSQL components.
the only wrong mapping Talend has shown to me; were against some views but they were just badly written with implicit casting.
thus i suggest you to use tMssqlOutput and download the ntlmauth.dll from jtds website if you want to use NT domain authentication.
hope it help.