One of the most common complaints that I hear from SQL DBAs with regards to ESS is that they don't trust it to perform INSERTs, UPDATEs, or DELETEs against the tables in their databases. I don't trust it either (and neither should you!). That's not a knock against FileMaker -- I simply don't trust any external process to interact directly with the tables. If that is what your DBA is doing (or proposing to do), you should ask them why.
Instead, they should provide a set of stored procedures for all of the INSERT, UPDATE, and DELETE operations that you'll need. That way, the data can be evaluated, business rules can be applied, and so on by the stored procedures -- and the stored procedures can perform the INSERTs, UPDATEs, and DELETEs.
Ideally, when getting things setup for you to access it via FileMaker, your SQL DBA should:
(1) Provide you with a SQL account that allows you read-only access to the data. If possible, your account should have SELECT privileges on a set a of VIEWs -- not on the base tables themselves. That way, through the definition of the VIEWs, they can limit the data that you can see and have already worked through any issues with regards to joining data in multiple tables. Using ESS, you can integrate these views into your FileMaker database (as table occurrences).
(2) Setup the SQL account so that it has EXECUTE privileges on a set of stored procedures that you can use to insert, update, and delete records. You can use the FileMaker "Execute SQL" script step to call the stored procedures and pass parameters to them. The issue is getting feedback from the store procedures. (For example, was the request successful, or did it fail, and if it failed, then why?). Some stored procedures will return parameters to you after they've executed and/or one or more data sets. The "Execute SQL" script step allows you to call stored procedures, but it doesn't provide you with a way to access the output from them. (There are ways to work around this. If you are interested in how I'm doing it, drop me a line.)
So, to summarize:
- Use ESS to view the data in the SQL databases. Never use it to insert, update, or delete records!
- Use the Execute SQL script step to call stored procedures to update data in the SQL databases.