tMap with dynamic length

Four Stars

tMap with dynamic length

Hi.

I am pretty new to using Talend as an ETL tool .

I am performing ETL from Sybase db -> tMap -> dboutput to MSSQL.

The problem I am facing is the schema for MSSQL. Say if I define the column to be of dtype string length 20, data mapped to this column will be forcefully created as a len 20 character, with unecessary white spaces .

May I know what are the available options for me to perform my ETL and ouput to MSSQL without this constraint i.e. taking the string from the source data as it is.

Thank you


Accepted Solutions
Ten Stars

Re: tMap with dynamic length

Dont use CHAR, but NVARCHAR as db type

All Replies
Nine Stars

Re: tMap with dynamic length

You can not have dbtype as String for MSSQL, either you need to select VARCHAR or NVARCHAR.

The String datatype could be from your source.

 

Now, when we define any column in Sql Server to be of length NVARCHAR(20) but store data into them let's say of 8 characters there will be no unnecessary white spaces be present in the column when you select the data from the column.

Ex: as you could see I stored 8 char so the length says 8 but DATALENGTH will show 16 which depicts the actual size being utilized.

DataLength-1.JPG

 

If you are seeing unnecessary white spaces in your destination side would recommend to TRIM the data (in tMap itself) and then perform the load.

Ten Stars

Re: tMap with dynamic length

Dont use CHAR, but NVARCHAR as db type
Four Stars

Re: tMap with dynamic length

Thanks for the assistance! 

 

Guess it was bad foundation when I didn't put much attention on data types. Have changed to VARCHAR and all is working fine! Thanks for the help, cheers!