• Home
  • Forums
  • FME Form
  • Data
  • SQL Server Writer - switch between Bulk Insert = Yes for reload and fme_db_operation for updates
Question

SQL Server Writer - switch between Bulk Insert = Yes for reload and fme_db_operation for updates

  • 21 September 2018
  • 7 replies
  • 73 views
tim_wood
Rank
Badge +7

I have configured a Workspace to process both resupplies and Change Only Updates (COUs). The data is written to SQL Server. Parent Workspaces handle pre and post processing tasks such as truncating the SQL table if it's a resupply.

I have created a "Resupply" parameter which is set to "Yes" for a resupply and "No" if it's a COU. This also determines whether Bulk Insert is set to Yes or No. However, because fme_db_operation is used as the Feature Operation value in the SQL Server Writer, Bulk Insert is ignored if it's a resupply, even though the only Feature Operation will be Insert. This results in a resupply taking 20 minutes longer to load (1 hour 13 mins instead of 53 mins).

I tried creating a "Feature Operation" parameter which would be set to "Insert" for a resupply or "fme_db_operation" for a COU, but this failed because it used "fme_db_operation" as the value for the Feature Operation, not as a reference to the fme_db_operation attribute.

Any ideas how I can get Bulk Insert to work without having to use 2 Writers? Could FeatureWriter help here?

7 replies

erik_jan
Rank
Userlevel 2
Badge +12

Use a Tester to check for the Bulk or not, before writing.

If Bulk is No then use the AttributeCretor to set fme_db_operation to Null, so it will be ignored.

(Can also be done using the AttributeCreator and Conditional values).

tim_wood
Rank
Badge +7

Use a Tester to check for the Bulk or not, before writing.

If Bulk is No then use the AttributeCretor to set fme_db_operation to Null, so it will be ignored.

(Can also be done using the AttributeCreator and Conditional values).

I assume you mean circle with a diagonal line <null> as per the screenshot below?

 

That didn't work - although the Feature Operation defaults to Insert, the fme_db_operation attribute is the source of the Feature Operation value in the Writer, so the translation still says "a conflicting Feature Operation has been detected ... Bulk Insert is now being set to No...".

 

My solution would be to use a conditional value for Feature Operation on the Writer, but it's not an option.

erik_jan
Rank
Userlevel 2
Badge +12

I assume you mean circle with a diagonal line <null> as per the screenshot below?

 

That didn't work - although the Feature Operation defaults to Insert, the fme_db_operation attribute is the source of the Feature Operation value in the Writer, so the translation still says "a conflicting Feature Operation has been detected ... Bulk Insert is now being set to No...".

 

My solution would be to use a conditional value for Feature Operation on the Writer, but it's not an option.

Odd. Have you tried setting fme_db_operation to INSERT (instead of Null) if Bulk = 'YES'?

 

 

erik_jan
Rank
Userlevel 2
Badge +12

I assume you mean circle with a diagonal line <null> as per the screenshot below?

 

That didn't work - although the Feature Operation defaults to Insert, the fme_db_operation attribute is the source of the Feature Operation value in the Writer, so the translation still says "a conflicting Feature Operation has been detected ... Bulk Insert is now being set to No...".

 

My solution would be to use a conditional value for Feature Operation on the Writer, but it's not an option.

I created a little test workspace.

 

See attachment.db-operation.fmw

 

 

tim_wood
Rank
Badge +7
Odd. Have you tried setting fme_db_operation to INSERT (instead of Null) if Bulk = 'YES'?

 

 

Yes. I think the problem is that because Feature Operation in the Writer is set to fme_db_operation, the Writer doesn't know if ALL the Feature Operations will be Insert. Therefore it has to set Bulk Insert to No just in case.

 

erik_jan
Rank
Userlevel 2
Badge +12
Yes. I think the problem is that because Feature Operation in the Writer is set to fme_db_operation, the Writer doesn't know if ALL the Feature Operations will be Insert. Therefore it has to set Bulk Insert to No just in case.

 

In that case you will probably need two (identical) writers: one using Bulk Insert and no fme_db_operation, the other using fme_db_operation. Then use the Tester (using the parameter) to write to the required writer.

 

 

tim_wood
Rank
Badge +7
Yes. I think the problem is that because Feature Operation in the Writer is set to fme_db_operation, the Writer doesn't know if ALL the Feature Operations will be Insert. Therefore it has to set Bulk Insert to No just in case.

 

That's what I wanted to avoid because of having to remember to do any schema changes to both Writers. I'm sure there's something clever that can be done with Dynamic Schemas, but I haven't quite got my head round those yet, and an extra 20 minutes loading time is acceptable if there's not an easy answer.

 

Reply


Community Stats

28,652
Posts
109,285
Replies
36,527
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.