Question

Force UPPER CASE on MSSQL writer

  • 5 January 2023
  • 6 replies
  • 3 views
A
Badge +1

I have comprehensive worskpace to write many tables in PostGIS, where all the target attributes are in lower case. Now I need to migrate this workspace to work also on MS SQL. I was able to change the writer and bulk update feature types table qualifier. The schemas are just similar to Postgres DB, but I'm getting errors because of the lower case of the attributes while writing to MS SQL.

Importing new feature types from schemas is not a way because I need to maintain the attribute mappings on every feature type. For example, the table name in lower case is not an issue but column names are. I would need that the writer forces UPPER CASE on writing to DB or bulk change all the attribute names in all writer feature type to UPPER CASE. I´m not feeling to do all the mappings again from the scratch, if the schemas are the same.

On the attached picture you can see the attributes from changed writer feature type (PostgreSQL->MSSQL, left) and new imported feature type (right). There is just manually edited the case of "FID" to check the error on writing.

Thank for advice, Jan

feature types

6 replies

dustin
Rank
Userlevel 3
Badge +26

You can use the BulkAttributeRenamer to change the case.

A
Badge +1

You can use the BulkAttributeRenamer to change the case.

But that would't be the solution, because I don't need to change the attribute cases on the way through the transformers, but in the writer feature type or on the actual output to database.

I could do this eventually if I import all the feature types again from the schema correctly, but the I would have to rework all the attribute mappings.

dustin
Rank
Userlevel 3
Badge +26

But that would't be the solution, because I don't need to change the attribute cases on the way through the transformers, but in the writer feature type or on the actual output to database.

I could do this eventually if I import all the feature types again from the schema correctly, but the I would have to rework all the attribute mappings.

I'm not sure I understand the issue. The BulkAttributeRenamer would be used right before your new MSSQL writer. All of your schema mapping and processing prior to that would remain the same.

 

EDIT: So, are you saying you manually updated all the attributes in your screenshot? Also, how are you doing your mappings? Were they done on the previous PostGIS writers themselves?

A
Badge +1

But that would't be the solution, because I don't need to change the attribute cases on the way through the transformers, but in the writer feature type or on the actual output to database.

I could do this eventually if I import all the feature types again from the schema correctly, but the I would have to rework all the attribute mappings.

That's the case I'd like to avoid. If I import new feature types I would need to adapt al of the processing in 30+ feature types because there is some manual attribute mapping, not all the attributes are mapped automatically by the name. If I attach new MSSQL writer the mapping is not retained. It would be the last option to rename all the passing attributes in Attribute manager an BulkRename.

Still it's a lot of work if there's a way to force the writer to upper case the attributes.

dustin
Rank
Userlevel 3
Badge +26

But that would't be the solution, because I don't need to change the attribute cases on the way through the transformers, but in the writer feature type or on the actual output to database.

I could do this eventually if I import all the feature types again from the schema correctly, but the I would have to rework all the attribute mappings.

Understood. For this reason, I normally avoid any processing in writer feature types. Performing the schema mapping in transformers prior to the writer allows for more flexibility down the road. Unfortunately, I can't think of a way to bulk change the case on the writer. Maybe someone can chime in with an idea.

A
Badge +1

But that would't be the solution, because I don't need to change the attribute cases on the way through the transformers, but in the writer feature type or on the actual output to database.

I could do this eventually if I import all the feature types again from the schema correctly, but the I would have to rework all the attribute mappings.

Ok, thanks anyway. So I guess I'll have redo the worspace somehow. I'll try SchemaMapper or AttributeManager to rename attributes according to target schema prior to the writer.

Reply


Community Stats

28,665
Posts
109,338
Replies
36,534
Members
Terms & ConditionsCookie settings
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.