Field length with dynamic schema columns

[Anonymous]

I'm currently attempting to use the Dynamic schema settings for columns (in a tFileInputDelimited) to read in a dynamically structured file and then add it into a staging table in an MS SQL Server DB before further processing.
This seems to work fine as it is but I'm losing rows through truncation whenever a field is over 100 characters. Is there a setting somewhere that I can override this 100 character limit?

[Anonymous]

I hadn't realised you could get the rejects from a DB output component so that might come in handy in the future. I assume that it kills performance if you disable batch mode though?
The reject is giving me the error that I expected, data truncation on these lines (they have a 103 character field).

[Anonymous]

Hi,

I assume that it kills performance if you disable batch mode though?

It may reduce job performance. But sometimes we need the rejected row error info from DB.
For exception of Data too long for column.
Please take a look at KB article TalendHelpCenterata too long for column. Let me know if it is working for you.
Best regards
Sabrina

[Anonymous]

I'm currently reading the data in and pushing it to the DB to stage it so I have dynamic columns on both the tFileInputDelimited and the tMSSqlOutput so I can't set the field length in my table (putting a length in a dynamic column doesn't seem to do anything).
Is there a way I can override this default 100 length somewhere or do I need to turn my staging database into a key value pair database?

[Anonymous]

Hi,

I'm currently reading the data in and pushing it to the DB to stage it so I have dynamic columns on both the tFileInputDelimited and the tMSSqlOutput so I can't set the field length in my table (putting a length in a dynamic column doesn't seem to do anything).
Is there a way I can override this default 100 length somewhere or do I need to turn my staging database into a key value pair database?

The max length of character is 255. You can have a try to turn your staging database into a key value pair database to see if it is working for your case. Please let me know your result.
Best regards
Sabrina

[Anonymous]

Is there a setting to apply to dynamic schema and change default size from 100 to 255.
the table i created using dynamic schema has all datatypes as varchar which is ok, but size is just 100. 
Isn't it supposed to be 255

[Anonymous]

Is there a setting to apply to dynamic schema and change default size from 100 to 255.
the table i created using dynamic schema has all datatypes as varchar which is ok, but size is just 100. 
Isn't it supposed to be 255

Bump

[Anonymous]

Hi,

the table i created using dynamic schema has all datatypes as varchar which is ok, but size is just 100. 
Isn't it supposed to be 255

Could you please post your schema setting screenshot into forum?
Best regards
Sabrina

[TomA]

Hi, I had the exact problem. I tried to use a tJavaFlex to set the column length, but could not find a method to do so.
In the end, I had to just let the table be created with the default length of 100 for each column, but limit the tFileInputDelimited to a single row.
Then used a tMSSQLColumnList to issue an alter table alter column statement for each column, to set the length to 4000. Then load all data from the source file, with truncate table action on the output table.
But I'd like to know if there is a more elegant way...

[Morpheus]

@xdshi , is there any elegant solution for this apart from adding an additional alter statement into the flow as suggested by @TomA ?