Skip to main content
  • EN

I am using FME Desktop 2014 build 14430 on Windows 2008
Server, and ArcGIS 10.3.1 and ArcServer 10.2.2 (loaded on same server).  

I have a table with a few fields (table name, feature
count, timestamp, error message, status) in my SQL Server SDE database that I
would like to have updated every time one of my FME jobs run.  Sounds very simple but for some reason I
can't figure it out. 

I tried using a Timestamper, StatisticsCalculator, Counter,
Variable Setter, SQL Creator, and shutdown python scripts. 
The transformers don't produce errors and nothing writes into the
table.  Yes I did fanout the attributes.

My SQL Creator script is:

INSERT INTO UPDATE_RECORD (LAYER, RUN_TIME, FEATURE_COUNT, STATUS, FAILURE_MSG)
VALUES('TEST', CURRENT_TIMESTAMP, 42, 'TRUE', 'SUCCESS');

This SQL fails, I think because it is a reader and not a writer??  Also that the ObjectID column flags an error that it can't accept NULLS...  I tried doing this as a SQL script to run after translation against this table but in a "non-spatial" connection and that failed as well.

My shutdown python scripts fail because it says it doesn't recognize the module arcpy.

FME_END_PYTHON: evaluating python script from
string...Python Exception <ImportError>: No module named
arcpy
Precondition failed at stfconf.cpp:1298. 
Expression: instance_ != 0

I also get a crash error. 

  Problem Event
Name:APPCRASH  Application
Name:fme.exe  Application
Version:2014.7.12.14430  Application
Timestamp:5433a4a6  Fault Module
Name:fme.dll  Fault Module
Version:2014.7.12.14430  Fault Module
Timestamp:5433a49b  Exception
Code:40000015  Exception
Offset:0000000000ccfadd  OS
Version:6.1.7601.2.1.0.274.10  Locale ID:1033  Additional
Information 1:33b2  Additional
Information 2:33b2f743a840af5774d03291871f1c1a  Additional
Information 3:c689  Additional
Information 4:c6896e4fc5d12bde6ee05af79fe17dab

I tried a fix found here:
https://knowledge.safe.com/articles/418/some-pyth...  for the individual script 

import os os.environx"PATH"]=""

But it didn't seem to fix it.  I can't do any server fixes because it's not my machine to mess with.  

Here is my python script: (PWS_counter is the global variable from the Counter I set inside the workflow, can I use that?)

import os
os.environs"PATH"] = ""

import time
now = time.strftime('%H%M%A%d%m%Y')

# FME variables.
import fme
status = fme.status
errorMsg = fme.failureMessage

import arcpy

# table to be updated
update_record_table = (r'C:\Users\USER\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\NDEP_VECTOR_SDEuser.sde\VECTOR.SDE.Update_record')

# Create Insert Cursor for multiple fields
rows = arcpy.InsertCursor(update_record_table, e'LAYER', 'FEATURE_COUNT', 'RUN_TIME', 'STATUS', 'FAILURE_MSG'])

row = rows.newRow()
row.setValue("LAYER", "FacilityManager_by_Wells")
row.setValue("FEATURE_COUNT", PWS_COUNTER)
row.setValue("RUN_TIME", now)
row.setValue("STATUS", status)
row.setValue("FAILURE_MSG", errorMsg)
rows.insertRow(row)
del row

So how can  I do a simple insert a row into a table in a SDE?  

Hi

I think the easiest would be to create a parallell track in your workspace. At the very end, before your Writer, connect a StatisticsCalculator and connect the Summary to a separate SDE Geodatabase feature type for your table UPDATE_RECORD. Insert an AttributeCreator between the two to set the values for RUN_TIME etc.

If you want to use ArcPy you will have to set a custom Python interpreter first, if you haven't already.

David

Have you tried the DatabaseUpdater transformer?

Hi

I think the easiest would be to create a parallell track in your workspace. At the very end, before your Writer, connect a StatisticsCalculator and connect the Summary to a separate SDE Geodatabase feature type for your table UPDATE_RECORD. Insert an AttributeCreator between the two to set the values for RUN_TIME etc.

If you want to use ArcPy you will have to set a custom Python interpreter first, if you haven't already.

David

Wow! That fixed the arcpy error! Thanks! I searched all day yesterday and couldn't find anything like that.

Have you tried the DatabaseUpdater transformer?

looked into it, but I can't figure out how to grab the FME variables like status and failure messages. However, these aren't totally important as actually writing into the table to show it ran, which I can't even get that far. I fanned out attribute, connected them, even made constant variables. It's not permissions issue, so I am not sure why.

Here is my final answer that I got to.

At the final step before the writer, I added a new workflow branch, starting with an AttributeCreator. I made 2 attributes, one naming the table I'm updating (fme_feature_type), and the dataset it is contained in. I added this onto all the workflows in my workspace (4 in this example).

Then I connected all 4 AttributeCreators to a StatisticsCalculator. I "grouped by" both the new attributes (to carry them thru the transformer). The Attributes to Analyze is fme_feature_type. Since Count is all I wanted, I deleted all the calculations except Count.

Then I connect the Stats to the Timestamper.

Then I connect that to my writer for this UPDATE_RECORD table, fanned out the attributes, and connected those up!

It works PERFECT!

Here is my final answer that I got to.

At the final step before the writer, I added a new workflow branch, starting with an AttributeCreator. I made 2 attributes, one naming the table I'm updating (fme_feature_type), and the dataset it is contained in. I added this onto all the workflows in my workspace (4 in this example).

Then I connected all 4 AttributeCreators to a StatisticsCalculator. I "grouped by" both the new attributes (to carry them thru the transformer). The Attributes to Analyze is fme_feature_type. Since Count is all I wanted, I deleted all the calculations except Count.

Then I connect the Stats to the Timestamper.

Then I connect that to my writer for this UPDATE_RECORD table, fanned out the attributes, and connected those up!

It works PERFECT!

Just as a side note, with the help of the FME support staff, I couldn't even get my variables passed through my python script. That's why I went this route. I couldn't grab error messages or anything doing it this way but I got what was important!

Reply


Community Stats

30,109
Posts
114,302
Replies
37,745
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.