Skip to main content
  • EN

Hi all,

I am fairly new to FME. What I wanted to implement I was able to figure out with the community here or the trainings at the Safe academy. But now I struggle as I do not understand, where my mistake is.

Goal: I need to write features with a dynamic schema. So, I added via Readers a “Reader as Resource”. It is an Excel file from where I want to use a specific sheet. There a added my columns which shall be written to my target source. Writing a FFS file works as expected by using the following configuration.

One important note: a sheet in excel has a limitation for the name. As I need to create a dynamic workbench which can read different source files and handle a different amount of dynamic columns per source file, I want to name it like the feature type - but shortened to what Excel can handle.

Dynamic Schema Definition configured and working

 

As I need also to write the same data to an EGDB, I use the JDBC format - with the same configuration: 

 

This does not work as expected. I have 2 observations.

First topic, the value given in “Schema Definition Name” is calculated as ‘’ - meaning empty string. I tried to write it in an attribute and using this one. No success. Putting the name of the sheet hard coded into the field “Schema Definition Name” - it magically works. But it should work dynamically, right?

Second topic, using the hard coded value and trying to get it run, I get an error message like

com.microsoft.sqlserver.jdbc.SQLServerException: The given value of type VARCHAR(22) from the data source cannot be converted to type varchar(13) of the specified target column.

The table in the EGDB is configured with column length varchar(255). Why do I get this error message? And where can I turn off to re-create the table or adjust the column type? Feature Operation is “Insert”, Table Handling is “Use Existing”.

I am using FME Workbench 2024.0, tested it with 2025.0 - same behaviour. Any ideas are appreciated!

Hi ​@susannel 

 

Rather than pointing to the error prompted, I would suggest two things to check the following alternatively to understand the issue whey Server is not accepting the data...

  1. Try the SQL Server writer without JDBC.
  2. Are you sure that the value content is varchar type? If the type of the data is matching then try by creating the table first in DB and then insert the data. Check whether data is inserted or not.

HI ​@pratap ,

thanks for your suggestions.

  1. I tested the MS Azure SQL Database Spatial. With the dynamic value for “Schema Definition Name” I get the same error message: ‘’ cannot be found. Hard coding the specific value delivers the error message Failed to create Azure table ‘name_of_table’ because no clustered index or primary key was specified. Even having Table Handling set to Use Existing this error occurs.
  2. Yes, the content is varchar. Table already existes and there the columns are defined with varchar(255).

Yes, pointing to the error is not a good way, but I would expect the transformer to work in both scenarios the same way. But it is not. I believe, this is a bug in FME.

Also, I am looking for a solution for my dynamic scenario.

Hi,

 

I suggest to check with writer “Microsoft SQL Server Spatial” and also create table while migrating 

Wow! The “Microsoft SQL Server Spatial” works - even with the existing table. There are some warnings about the column format, but the data were written. Thanks so much for this tip!

I used “MS Azure SQL Database Spatial” as our database is located in Azure. But that it makes such a difference is unbelievable to me. Azure and JDBC do not work, but the pure SQL Server one does.

Still, the error with the “Schema Definition Name” exists. As I have now the data in the database, the most stress is relieved.

Community Stats

31,897
Posts
121,119
Replies
39,512
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.