Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

[Anonymous]

Hello
Anyone got ETL playing nicely with GUIDs? Out of the box it detects the schema as 36 length varbinaries rather than 16 bit byte arrays, or worse booleans!
I tried changing the mappings (Preferences > Talend > Specific Settings > Metadata of TalendType) but it doesn't seem to help. I adjusted the Java section of MSSQL mapping to:

				<dbType type="UNIQUEIDENTIFIER">
					<talendType type="byte[]" default="true" />

But when I retrived the schema I still get a db type VARBINARY and type Object length 36

Handling GUIDs in MySQL is even worse. For Mysql I resorted to doing a find and replace in the *.item file because I couldn't find a better solution.

FIND AND REPLACE
length="16" sourceType="BIT" talendType="id_Boolean"
WITH
length="16" sourceType="BINARY" talendType="id_byte[]"

Surely there are other people who have overcome this problem
Cheers
Kevin

[Anonymous]

Hi Kevin,
If you changed the data type matching file, it should have worked. Have you restarted the Studio after doing that?

[Anonymous]

Thanks Vincent. Yes I have restarted but I think the problem that it is mapping VARBINARY not UNIQUEIDENTIFIER. Same with MySQL using BIT.

[Anonymous]

So why does ETL get the schema wrong for connection metadata (using Retrieve Schema) and why can we not fix the 'DB Type' without hacking the .item file?
Should I start a new post?

[Anonymous]

Turns out we can fix it, as of version 5.3 you must do so yourself:
Window > Preferences >Specific Settings > Metadata of Talend and edit mapping_Mysql.xml

<language name="java">
	<talendToDbTypes><!-- Adviced mappings  -->
		...
		<talendType type="id_byte[]">
			<dbType type="BINARY" default="true"/>
	<dbToTalendTypes>
		...
		<dbType type="BINARY">
			<talendType type="id_byte[]" default="true" />

[Anonymous]

Hi kcd,
Thanks for your feedback and sharing your solution with us.
Best regards
Sabrina

[Anonymous]

Hello Expert,
Any one can elaborate on this.
i am reading data from delimited file and i want to insert data into MSSql table.
Table structure already their in SQL server db i want to load data into existing table for data type uniqueidentifier 
how to convert int to uniqueidentifier  in tmap or MSSqloutput.
I tried with int,object but it won't work, any help would be much appreciated.
 

[sasi8008]

Hi,
Any one can elaborate on this.
i am reading data from mssql database table it's has UniqiueIdentifier field,and i want to insert data into MSSql table.
how to convert int to uniqueidentifier  in tmap or MSSqloutput.
I tried with int,object but it won't work, any help would be much appreciated.

[Anonymous]

Hi,
read it as Object from source keep object data type in tmap.
You have to change in mssqloutput --> edits schema DB type="UNIQUEIDENTIFIER"
let me know for further help.