Change destination DB schema in dynamic workflow

Question

I’m struggling with dynamic worflow when i want to change the target MS SQL DB schema (table qualifier). When i want to migrate data between databases with the same DB schema, it’s fine. But when i want to change the schema i’m running into errors. So far I could manage to migrate data from custom schema (e.g. “ldm”) to default “dbo” schema manipulating “fme_feature_type” and fme_feature_type_name” (also not sure about the differnce of these 2 attributes) in Attribute Manager assigning just Table name and omitting the schema qualifier.
When i want to use another target schema than “dbo” (e.g. “test”) i always run into error:

Microsoft SQL Server Spatial Writer: A feature has arrived with 'fme_feature_type' of test.OPSiteEK, but the feature type is not recognized. Halting translation

1) Does anyone know how to reliably change the target schema for dynamic worflow?
2) I have also noticed that if I want drop and recreate tables with no data, i have to send at least 1 data feature, because if i sent just Schema features, the target tables are not recreated at all and stay with the old data.

Thanks, Jan

albinepro · Answer

So in the end I was able to progress in this matter:
1) I setup `fme_feature_type_name` on Data and Schema features manually trough AttributeManager to <schema_name>.<table_name>, schema is coming from workspace User parameter.

Later i feed Schema and Data features to FeatureWriter with this setup:

2) In order not to rely just on FME 2025 feature, for each Schema record that Im reading a make a dummy Data feature resulting in:

In this setup it works as expected, so its omitting read table schema and assign new schema for the target DB from a user parameter. I have to send one dummy Data feature in order to create the tables, that I truncate afterwards with SQLExecutor.

Change destination DB schema in dynamic workflow

1 reply

Community Stats

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute