Solved

Dynamic output SQL server , attribute renamer not working

boubcher
Rank
Badge +10

Hello there

we have a simple WS GeoDb reader and SQL server writer and we are only renaming 2 attributes as shown

but the table created on SQL still keep the same name as the reader ignoring the renamer transformer

is there any reason why?

icon

Best answer by takashi 18 July 2018, 08:38

View original

11 replies

danilo_fme
Rank
Userlevel 4
Badge +30

Hi @boubcher

I simulated here to read a Geodatabase, but its works similar.

After the transformer AttributeRenamer I think you could use the transformer AttributeExposer and exponse the two old Attributes:

Shape_Area

Shape_Length

In the Writer - tab User Attributes - Please write the two new attributes but let the option Attribute Definition in Dynamic.

 

It was worked for me.

Thanks,

Danilo

 

 

boubcher
Rank
Badge +10

@Danilo

Thanks for your response

it did work but by only selecting Automatic in the Attributes

userattributes-1.png

userattributes-2.png

then back to Dynamic

userattributes-3.png

but in the database we are getting duplication of both attribute, I have no idea why is that

userattributes-4.png

takashi
Rank
Userlevel 2
Badge +17

Hi @boubcher,

  1. In your settings, the destination schema will be derived from the source geodatabase schema definition.
  2. The AttributeRenamer just renames attributes which are contained by each feature, it won't change destination schema.
  3. If you need to modify the destination schema, you will have to derive schema from other resource (external dataset, lookup table, list attribute, etc.) which contains your desired schema. See the Tutorial: Dynamic Workflows to learn more.
  4. However, in some cases, you can partially modify the destination schema through the writer feature type properties.
  5. In your case, the following workflow and writer feature type configuration might help you.

boubcher
Rank
Badge +10

Hi @boubcher,

  1. In your settings, the destination schema will be derived from the source geodatabase schema definition.
  2. The AttributeRenamer just renames attributes which are contained by each feature, it won't change destination schema.
  3. If you need to modify the destination schema, you will have to derive schema from other resource (external dataset, lookup table, list attribute, etc.) which contains your desired schema. See the Tutorial: Dynamic Workflows to learn more.
  4. However, in some cases, you can partially modify the destination schema through the writer feature type properties.
  5. In your case, the following workflow and writer feature type configuration might help you.

 

@takashi

 

its works

 

is there a way we could specify a specific schema name to write to, by default its writing to Dbo , I do have a mapping of each table and it corresponding schema name in Sqlserver

 

 

takashi
Rank
Userlevel 2
Badge +17

 

@takashi

 

its works

 

is there a way we could specify a specific schema name to write to, by default its writing to Dbo , I do have a mapping of each table and it corresponding schema name in Sqlserver

 

 

Please see again this Q&A;: "Dynamic" output to SQL Server - no Table Qualifier/database schema ?

 

 

danilo_fme
Rank
Userlevel 4
Badge +30

@Danilo

Thanks for your response

it did work but by only selecting Automatic in the Attributes

userattributes-1.png

userattributes-2.png

then back to Dynamic

userattributes-3.png

but in the database we are getting duplication of both attribute, I have no idea why is that

userattributes-4.png

Hi @boubcher

 

 

I'm sorry. I checked now your answer because we have difference time. But, I'm happy that @takashi

 

boubcher
Rank
Badge +10

Hi @boubcher,

  1. In your settings, the destination schema will be derived from the source geodatabase schema definition.
  2. The AttributeRenamer just renames attributes which are contained by each feature, it won't change destination schema.
  3. If you need to modify the destination schema, you will have to derive schema from other resource (external dataset, lookup table, list attribute, etc.) which contains your desired schema. See the Tutorial: Dynamic Workflows to learn more.
  4. However, in some cases, you can partially modify the destination schema through the writer feature type properties.
  5. In your case, the following workflow and writer feature type configuration might help you.

@takashi

 

I did see the article before the question but its shows a fixed schema for the entire table

 

"(schema_name.@Value(fme_feature_type)"

 

in our case, we have for each feature type ( table ) a different schema as per the excel

 

schema-table-relation.png

 

takashi
Rank
Userlevel 2
Badge +17

Hi @boubcher,

  1. In your settings, the destination schema will be derived from the source geodatabase schema definition.
  2. The AttributeRenamer just renames attributes which are contained by each feature, it won't change destination schema.
  3. If you need to modify the destination schema, you will have to derive schema from other resource (external dataset, lookup table, list attribute, etc.) which contains your desired schema. See the Tutorial: Dynamic Workflows to learn more.

  4. However, in some cases, you can partially modify the destination schema through the writer feature type properties.
  5. In your case, the following workflow and writer feature type configuration might help you.

0684Q00000ArKaSQAV.png

0684Q00000ArKe9QAF.png

0684Q00000ArKaNQAV.png

No problem. If the features have the destination schema name and table name as attributes - e.g. "table_schema" and "table_name", you can just set this expression to the Table Name field.

 

@Value(table_schema).@Value(table_name)
boubcher
Rank
Badge +10

Hi @boubcher,

  1. In your settings, the destination schema will be derived from the source geodatabase schema definition.
  2. The AttributeRenamer just renames attributes which are contained by each feature, it won't change destination schema.
  3. If you need to modify the destination schema, you will have to derive schema from other resource (external dataset, lookup table, list attribute, etc.) which contains your desired schema. See the Tutorial: Dynamic Workflows to learn more.
  4. However, in some cases, you can partially modify the destination schema through the writer feature type properties.
  5. In your case, the following workflow and writer feature type configuration might help you.

@takashi

 

thanks so Much, I did use the feature merger to merge with the excel containing the schema name and the feature type name, and its working.

 

no the new attribute we created is giving an error ( Existing table for feature type `Adminstrative_Boundary.City_Boundary' does not have attribute with name `New_attribute'.)

 

during the process, we are looking to add the same attribute to all tables

 

 

 

 

 

takashi
Rank
Userlevel 2
Badge +17

Hi @boubcher,

  1. In your settings, the destination schema will be derived from the source geodatabase schema definition.
  2. The AttributeRenamer just renames attributes which are contained by each feature, it won't change destination schema.
  3. If you need to modify the destination schema, you will have to derive schema from other resource (external dataset, lookup table, list attribute, etc.) which contains your desired schema. See the Tutorial: Dynamic Workflows to learn more.
  4. However, in some cases, you can partially modify the destination schema through the writer feature type properties.
  5. In your case, the following workflow and writer feature type configuration might help you.

The "Adminstrative_Boundary.City_Boundary" table which doesn't contain the column called "New_attribute" seems to exist already in the destination database. Generally FME writers cannot modify schema of existing tables.

 

If you are going to create a new table by running the workspace, just drop the existing table beforehand.

 

If you have to keep records which are stored in the existing table, you will have to add a column to the table with an appropriate SQL statement beforehand.

 

 

boubcher
Rank
Badge +10

Hi @boubcher,

  1. In your settings, the destination schema will be derived from the source geodatabase schema definition.
  2. The AttributeRenamer just renames attributes which are contained by each feature, it won't change destination schema.
  3. If you need to modify the destination schema, you will have to derive schema from other resource (external dataset, lookup table, list attribute, etc.) which contains your desired schema. See the Tutorial: Dynamic Workflows to learn more.
  4. However, in some cases, you can partially modify the destination schema through the writer feature type properties.
  5. In your case, the following workflow and writer feature type configuration might help you.

@takashi

 

 

Thanks So Much working Now and did drop all tables and start over.

 

I think we should add all those cases to the Tutorial :)

 

 

Reply


Community Stats

28,667
Posts
109,345
Replies
36,535
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.