Hi,
my recommendation is that you contact Safe support (
https://safecommunity.force.com/knowledgeSubmitCase) with your question, perhaps they have some whitepapers or other information for you that specifically targets SSIS or similar tools.
I do not know SSIS in particular, but in general I'd say there are a few differences between FME and other enterprise ETLs. FME is database-agnostic with a strong focus on spatial data, whereas most(?) other enterprise ETLs are the other way around, if they support spatial data other than as BLOBs at all.
Some of my thought on FME as an enterprise ETL tool:
- Actions are feature-dependent. Target tables will not be created, truncated or dropped unless a feature enters it. This makes it rather complicated to e.g. replicate empty feature classes. (It is possible, but it's a bit hack-ish and I hope this will be improved)
- NULL values used to behave nothing like you're used to from databases like Oracle or MSSQL, but this has been vastly improved in FME 2014, fortunately. So make sure that you stick to this version or newer for your recommendations.
- Exception handling can be a bit fragile if you do not plan for it properly, one error can easily topple the whole process and the recovery isn't always easy / demands manual work. This hs is fortunately much better with FME Server, though, so make sure to focus on that rather than on automating anything important using FME Desktop.
- Data migration can in some cases be slow compared to some database-specific ETL tools, as FME has to extract, locally process and then rewrite all the data, even if the source and target database is the same. Some database-specific tools are able to do the processing within the database, which can be an order of magnitude quicker. The SQLExecutor transformer can be a powerful ally here, if you know how to use it for best effect.
That being said, FME is a very powerful tool on which you can easily build incredibly feature-rich processes. For enterprise ETL, I'm using a lot of PythonCallers and SQLExecutors, which I think are key to advanced functionality that's not available out of the box in FME. For geometric operations and spatial operators, I do not think there is anything that comes even close to FME in terms of features or ease of use.
Hope this helps.
David
We're looking at this now - and given the nature of the data is inherently spatial, we're probably going to err on the side of FME, despite possible a performance drop.
