Field length with dynamic schema columns

One Star

Field length with dynamic schema columns

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?
One Star

Re: Field length with dynamic schema columns

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).
Moderator

Re: Field length with dynamic schema columns

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 TalendHelpCenterSmiley Very Happyata too long for column. Let me know if it is working for you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Field length with dynamic schema columns

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?
Moderator

Re: Field length with dynamic schema columns

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Field length with dynamic schema columns

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
One Star

Re: Field length with dynamic schema columns

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
Moderator

Re: Field length with dynamic schema columns

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: Field length with dynamic schema columns

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...