Writing Data to SQL Spatial Database Results in Successful Translation With No Actual Data Written/Updated

A base level check is to look at the embedded Connection details for the Writer(s).

If the workspace is using in the Writer a Named Database Connection instead of embedding the connection details into the Writer,  then you can get differences between machines if they use a different SQL Server instance name, database, authentication method etc. for the same Database Connection name.

 

One basic check…..although I would highly recommend building/testing this first outside your Production databases….is to use a SQLCreator and trial from the machine basic SQL like:

UPDATE table_name SET test_field_name = test_value WHERE row_identifer_field = row_identifier_value

This is a very basic check that SQL statements executed on the FME machine (which is what is really happening behind the scenes in a SQL Server Writer) and workspace /Database Connection definitions will successfully complete the transaction in the expected Database/Table.   As above though…….don’t execute these in your Production data until tested.  But a really basic 1 row UPDATE to a non-critical field value that is easily rolled back can validate that the Connection from FME to SQL Server is pointing to the right location and is writing data.

Hi, and thanks for the reply.

The database is definitely correct, I am using the embedded database connections from FME options, and have checked that it’s set up correctly. I also note that when trying the workspace with other colleagues, that they had to update the readers and writers to suit their database connection names. I am also able to read data from it correctly as I’ll elaborate further down.

As advised, I tried a basic update query in SQL Creator (good idea by the way) with this query:

UPDATE Conquest_Kerbing SET Length = 999 WHERE AssetID = '95961';

 and it behaved in the same way as the writers had been, where it just wasn’t applying the change, and the Length attribute for this record is still as it originally was (equal to 34), even though it’d tell me that the query executed successfully. I then used that same query within SQL Server Management Studio (SSMS) and it worked right away. After this, I changed the SQL Creator transformer within FME to a select query, whilst exposing the attributes “AssetID” and “Length”:

SELECT * FROM Conquest_Kerbing
WHERE AssetID = '95961';

to see if it was able to read the value applied by the update query in SSMS and it came back with the expected result right away.

Can you share your log? It is possible that something is coming back from the database indicating what is not quite right with the data youre submitting

Can you share your log?

Hi, no problem at all. I just had to censor certain parts which would expose my name or where I work. These parts have just been replaced with hashes.

    Command line to run this workspace:
        "C:\Program Files\FME\fme.exe" C:\TEMP\Conquest_SQLDB_To_SA_GIS1_GISDB_Insert_1.fmw
          --XMIN "278355"
          --XMAX "362058"
          --YMIN "5839882"
          --YMAX "5921931"
          --SourceDataset_MSSQL_SPATIAL_3 "Asset_GISDB_Working"
          --DestDataset_MSSQL_SPATIAL_5 "AssetGISDB_Test"
          --FME_LAUNCH_VIEWER_APP "YES"
    
Starting translation...
Current FME version: FME 2024.0.1.0 (20240328 - Build 24202 - WIN64)
System Time: 20241113235550 UTC
FME_HOME is 'C:\Program Files\FME\'
FME Form (floating)
Temporary License: 259 days left.
Machine host name is: LT-ZWRIGHT2
OS Locale Name     : en_AU
OS Locale Encoding : windows-1252
Process Encoding   : UTF-8
FME API version: '4.0 20240212'
FME Configuration: FME_BASE is 'no'
FME Configuration: FME_MF_DIR is 'C:\TEMP/'
FME Configuration: FME_MF_NAME is 'Conquest_SQLDB_To_SA_GIS1_GISDB_Insert_1.fmw'
FME Configuration: FME_PRODUCT_NAME is 'FME(R) 2024.0.1.0'
Operating System: Microsoft Windows 10 64-bit  (Build 19045)
FME Platform: WIN64
System Status: 3.24 GB of disk space available in the FME temporary folder (C:\Users\#######\AppData\Local\Temp\wbrun_1731478849242_25292\fmetmp_4)
System Status: 15.35 GB of physical memory available
Failed to free sufficient memory to reach the process usage limit. To improve stability and performance please increase the memory available to FME. Available memory can be increased by adding physical RAM, increasing swap space, or closing other applications
System Status: 61.39 GB of virtual memory available
START - ProcessID: 31836, peak process memory usage: 45928 kB, current process memory usage: 45928 kB
FME Configuration: Command line arguments are `C:\Program Files\FME\fme.exe' `C:\TEMP\wb-xlate-1731542147851_25292' `LOG_STANDARDOUT' `YES' `LOG_TIMINGS' `YES' `LogCountServerName' `52546' `LOG_STANDARDOUT' `No' `WORKBENCH_LOG_SERVER' `51847'
FME Configuration: Connection Storage: 'C:\Users\#######\AppData\Roaming\Safe Software\FME\'
Shared folders for formats are : C:\Program Files\FME\datasources;C:\Users\#######\OneDrive - #######\User_Folder\Documents\FME\Formats
Shared folders for transformers are : C:\Users\#######\AppData\Roaming\Safe Software\FME\Packages\24202-win64\transformers;C:\Program Files\FME\transformers;C:\Users\#######\AppData\Roaming\Safe Software\FME\FME Store\Transformers
Shared folders for coordinate systems are : C:\Users\#######\OneDrive - #######\User_Folder\Documents\FME\CoordinateSystems
Shared folders for coordinate system exceptions are : C:\Users\#######\OneDrive - #######\User_Folder\Documents\FME\CoordinateSystemExceptions
Shared folders for coordinate system grid overrides are : C:\Users\#######\OneDrive - #######\User_Folder\Documents\FME\CoordinateSystemGridOverrides
Shared folders for CS-MAP transformation exceptions are : C:\Users\#######\OneDrive - #######\User_Folder\Documents\FME\CsmapTransformationExceptions
Shared folders for transformer categories are : C:\Users\#######\OneDrive - #######\User_Folder\Documents\FME\TransformerCategories
FME Configuration: Reader Keyword is `MULTI_READER'
FME Configuration: Writer Keyword is `PRIMARY_DEST'
FME Configuration: Writer Group Definition Keyword is `PRIMARY_DEST_DEF'
FME Configuration: Reader type is `MULTI_READER'
FME Configuration: Writer type is `MULTI_WRITER'
FME Configuration: No destination coordinate system set
FME Configuration: Current working folder is `C:\TEMP'
FME Configuration: Temporary folder is `C:\Users\#######\AppData\Local\Temp\wbrun_1731478849242_25292\fmetmp_4', set from environment variable `FME_TEMP'
FME Configuration: Cache folder is 'C:\Users\#######\AppData\Local\Temp'
FME Configuration: FME_HOME is `C:\Program Files\FME\'
FME Configuration: Extremely low initial memory conditions detected. To improve stability and performance please increase the memory available to FME. Available memory can be increased by adding physical RAM, increasing swap space, or closing other applications
Creating writer for format: 
Creating reader for format: 
MULTI_READER(MULTI_READER): Will fail with first member reader failure
MULTI_READER(MULTI_READER): Adding MSSQL_SPATIAL Reader with keyword MSSQL_SPATIAL_5
Using Multi Reader with keyword `MULTI_READER' to read multiple datasets
FME Configuration: Using connection values from 'Asset_GISDB_Working' for MSSQL_SPATIAL_5
Creating reader for format: Microsoft SQL Server Spatial
Trying to find a DYNAMIC plugin for reader named `MSSQL_SPATIAL'
FME Configuration: Source coordinate system for reader MSSQL_SPATIAL_5[MSSQL_SPATIAL] set to `EPSG:7855'
Coordinate System `EPSG:7855' parameters: CS_NAME=`MGA/20-55' DESC_NM=`Map Grid of Australia Zone 55, GDA2020 (7Parameter)' DT_NAME=`GDA2020-7P' EPSG_QD=`1' GROUP=`AUSNZ' MAP_SCL=`1' MAX_LAT=`-8.216666666666667' MAX_LNG=`150.31666666666666' MIN_LAT=`-51.88333333333333' MIN_LNG=`143.68333333333334' PARM1=`147' PROJ=`TM' QUAD=`1' SCL_RED=`0.9996' SOURCE=`EPSG 9.0' UNIT=`METER' X_OFF=`500000' Y_OFF=`10000000'
Loaded module 'MSSQL_SPATIAL' from file 'C:\Program Files\FME\plugins/FME_ADO.dll'
FME API version of module 'FME_ADO' matches current internal version (4.0 20240212)
Microsoft SQL Server Spatial Reader: Read 1 DEF line(s).  Found 21 attribute(s)
Microsoft SQL Server Spatial Reader: Opening `AssetGISDB_Working' for read operation
Microsoft SQL Server Spatial Reader: Trying to connect using Microsoft OLE DB Driver 19 for SQL Server...
Microsoft SQL Server Spatial Reader: 'SqlServer.Types' library loaded matched expected version '15.0.0.0'
MSSQL_SPATIAL reader: Creating bulk features from individual features
Using MultiWriter with keyword `PRIMARY_DEST' to output data (ID_ATTRIBUTE is `multi_writer_id')
Writer output will be ordered by value of multi_writer_id
Loaded module 'LogCount_func' from file 'C:\Program Files\FME\plugins/LogCount_func.dll'
FME API version of module 'LogCount_func' matches current internal version (4.0 20240212)
Loaded module 'QueryFactory' from file 'C:\Program Files\FME\plugins/QueryFactory.dll'
FME API version of module 'QueryFactory' matches current internal version (4.0 20240212)
Loaded module 'FeatureJoinerFactory' from file 'C:\Program Files\FME\plugins/FeatureJoinerFactory.dll'
FME API version of module 'FeatureJoinerFactory' matches current internal version (4.0 20240212)
Loaded module 'CounterFactory' from file 'C:\Program Files\FME\plugins/CounterFactory.dll'
FME API version of module 'CounterFactory' matches current internal version (4.0 20240212)
Microsoft SQL Server Spatial Reader: Spatial column not specified for Feature Type `dbo.Conquest_Kerbing'. Using Column `GEOM' with type `geometry'
Microsoft SQL Server Spatial Reader: Now reading features from table dbo.Conquest_Kerbing, 10 record(s) at a time
The EPSG number of the FME coordinate system 'MGA/20-55' is '7855'
NOT changing coordinate system of reader identified by keyword `MSSQL_SPATIAL_5' from `EPSG:7855' to `MGA/20-55' -- mapping file setting of `EPSG:7855' overrides coordinate system `MGA/20-55' read from file
5379: Database read complete.  Retrieved <default parameter> feature(s)
FME Configuration: Using connection values from 'Conquest_Cloud_Prod' for R_1
Creating reader for format: Microsoft SQL Server Non-Spatial
Trying to find a DYNAMIC plugin for reader named `MSSQL_ADO'
Performing query against MSSQL_ADO dataset `Conquest_Cloud_Prod'
MSSQL_ADO reader: Creating bulk features from individual features
Microsoft SQL Server Non-Spatial Reader: Opening `Conquest_Cloud_Prod' for read operation
Microsoft SQL Server Non-Spatial Reader: Trying to connect using Microsoft OLE DB Driver 19 for SQL Server...
MSSQL_ADO reader: Creating bulk features from individual features
96342: Database read complete.  Retrieved <default parameter> feature(s)
Microsoft SQL Server Spatial Reader: Closing `AssetGISDB_Working' . Read operation complete
MULTI_READER(MULTI_READER): Done reading 5379 features from 1 readers
Emptying factory pipeline
Router and Unexpected Input Remover (RoutingFactory): Tested 5379 input feature(s), wrote 5379 output feature(s): 5379 matched merge filters, 5379 were routed to output, 0 could not be routed.
Unwanted Router Input Nuker (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)
dbo.Conquest_Kerbing_MSSQL_SPATIAL_5 Splitter (TeeFactory): Cloned 5379 input feature(s) into 5379 output feature(s)
SQLCreator_Creator (CreationFactory): Created 1 features
Microsoft SQL Server Non-Spatial Reader: Closing `Conquest_Cloud_Prod' . Read operation complete
SQLCreator_RESULT Brancher -1 374 (BranchingFactory): Branched 96342 input features -- 1 feature routed to the target factory, and 0 features routed to the fallback factory.
_CREATOR_BRANCH_TARGET (TeeFactory): Cloned 101721 input feature(s) into 101721 output feature(s)
ResourceManager: Optimizing Memory Usage.  Please wait...
... Last line repeated 86 times ...
AttributeRemover_48 (TeeFactory): Cloned 5379 input feature(s) into 5379 output feature(s)
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                        `CountFactory' Domain Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
counter                                                                   5379
==============================================================================
Total features counted:                                                   5379
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Destination Feature Type Routing Correlator (RoutingFactory): Tested 5379 input feature(s), wrote 5379 output feature(s): 0 matched merge filters, 5379 were routed to output, 0 could not be routed.
Final Output Nuker (TeeFactory): Cloned 5379 input feature(s) into 0 output feature(s)
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
dbo.Conquest_Kerbing                                                      5379
==============================================================================
Total Features Read                                                       5379
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 3 warning(s) (0 feature(s) output)
FME Session Duration: 7.2 seconds. (CPU: 5.2s user, 0.9s system)
END - ProcessID: 31836, peak process memory usage: 195420 kB, current process memory usage: 128752 kB
Translation was SUCCESSFUL