I feel like I’ve seen this done before, but can’t put my finger on how to do it…..
A warning, I’m working with 2020.2 so cast your mind back a few years…..
Essentially I have a workspace that is taking data from MSSQL and writing it out to SQLite. In theory this is just a straight Reader to Writer.
However, the SQLite file is being created fresh each time the process runs and it’s creating any number of tables based on relationships within the MSSQL database - these relationships need to be maintained. Therefore, the schema in the SQLite table is being created with an SQLExecutor and CREATE TABLE statements.
Currently, I’m reading the schema from MSSQL and manually mapping each attributes data type from the MSSQL definition to SQLite using an AttributeValueMapper.
Now it wouldn’t take too much effort to make this list exhaustive, but there is surely a better way.
I initially thought of using a schema attribute and let FME do the MSSQL→ FME→ SQLite mapping with a dynamic writer, and that worked. But in SQLite you can only add relationships when you’re creating the table, you can’t add a relationship retrospectively. So that option doesn’t actually get me where I want to be.
FME knows what MSSQL types map to what SQLite types when using readers and writers, so this information must be somewhere. I had a bit of a look through the function and factories and python API to see if anything jumped out at me that would facilitate this - I couldn't see anything immediately.
Thoughts?
