• Home
  • Forums
  • FME Form
  • Data
  • How can you still load data that does not violate a db unique constraint using a sql to sql fmw?
Question

How can you still load data that does not violate a db unique constraint using a sql to sql fmw?

  • 9 October 2019
  • 5 replies
  • 23 views
T
Badge

I have a workspace that simply loads data from a staging table to a table in our ERP schema. It has a unique constraint that should not allow duplicates. I want the fmw to load all records that do not violate the constraint and just ignore the bad records. I have the REJECTED FEATURE HANDLING set to CONTINUE TRANSLATION. I would expect to just load all records that do not violate the db constraint. However, the fmw just fails and no data is loaded.

How can I get this to load valid records and ignore those that violate the constraint?

5 replies

itay
Rank
Badge +16

If you are worried about duplicates you can use the DuplicateFilter, tricky part will be to decide which of the duplicates you need to keep.

What kind or errors are you getting? Could you post a logfile snippet with the errors?

T
Badge

Here is the error section of the log.

 

Unexpected Input Remover Nuker (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)

dbo.lps_hr_crseunit_staging (MSSQL_ADO_1) Splitter (TeeFactory): Cloned 15 input feature(s) into 15 output feature(s)

dbo.lps_hr_crseunit_staging_MSSQL_ADO_1 Feature Counter -1 4 (TeeFactory): Cloned 15 input feature(s) into 15 output feature(s)

Destination Feature Type Routing Correlator (RoutingFactory): Tested 15 input feature(s), wrote 15 output feature(s): 0 matched merge filters, 15 were routed to output, 0 could not be routed.

Final Output Nuker (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)

Microsoft SQL Server Non-Spatial Writer: Failed to commit database transaction. Provider error `(2627) Violation of UNIQUE KEY constraint 'UK_lps_hr_crseunit'. Cannot insert duplicate key in object 'dbo.hr_crseunit'. The duplicate key value is (104099 , Oct 7 2019 12:00AM, FME_PM_UPLD2, 2019100500, Colorado Language Arts Society, , 0.53).'

Microsoft SQL Server Non-Spatial Writer: Closing `lps_bp' . Write operation complete

A fatal error has occurred. Check the logfile above for details

... Last line repeated 2 times ...

Translation FAILED with 2 error(s) and 1 warning(s) (15 feature(s) output)

FME Session Duration: 3.0 seconds. (CPU: 0.3s user, 0.2s system)

END - ProcessID: 4868, peak process memory usage: 137692 kB, current process memory usage: 44708 kB

A fatal error has occurred. Check the logfile above for details

Program Terminating

Translation FAILED.

T
Badge

I do have a duplicate filter on the initial file load to filter out dupes based on unique record criteria. However, the table has rows in it already and that is where I want to make sure I don't load dupes. I put constraints on the table to ensure no dupes but was hoping in the fmw somehow I could have it just ignore the dupe errors and load only records without dupes.

 

 

itay
Rank
Badge +16

FME can do a lot, but not what you are trying, well not this way.

I would read the file and table and join the features (essentially removing duplicates) from the file so that you are left with unique features that are not present in the table.

M
Badge +2

@tgolden The translation fails despite the Rejected Feature Handling: Continue Translation (found under Workspace Parameters->Translation) because Regected Feature Handling only applies to the <rejected> port on transformers, not to writers.

The database will fail the entire translation when using Bulk Insert: Yes. Try setting Bulk Insert: No on your SQL Server writer. This will be a bit more forgiving, although the load will be a little slower.

Reply


Community Stats

28,753
Posts
109,617
Replies
36,587
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.