Create then add foreign key for CSV reader

Create the FK in a SQLExecutor instead of a SQLCreator. The SQLExecutor is placed in the flow after the CSV reader and will execute a SQL statement for each row in the CSV. The returned attribute(s) will be added to the read features.

As Erik_jan said you are missing the sql executor in your work flow this will trigger the SQL statement for each of your incoming features. Safe have a good article about the differences in the 2 transformers which may help you

https://knowledge.safe.com/articles/23528/sqlcreat...

erik_jan and ciarab

I've reviewed the SQLExecutor and don't know what to use for the Initiator. The obvious input is the CSV, but that runs the SQL statement for each record, giving each row a different BatchID. Everything in the CSV should be linked to the same batch.

If you want to generate 1 new batch ID from a sequence in a database you can do the following:

Use a Creator with the option "Create at End" = No

Add a SQLExecutor to generate the Batch ID.

Add an UnconditionalFeatureMerger from the FME store to add the Batch ID to the CSV records.

See attached picture:

Yea, that is it! Didn't know the Creator or UnconditionalFeatureMerger transformers existed. Both will be very useful. I can't write the output to the SQL Server database so assume there is something wonky in my writer. When I connect an inspector or write to a CSV both include the single BatchID from the SQLExecutor. Thanks a lot for your help!

That's great. I'll just mention that you can use the regular FeatureMerger (instead of the UnconditionalFeatureMerger) by putting a fixed value into the source/destination key fields.

Mark2AtSafe - very clever. I tried to use the FeatureMerger before but couldn't figure out how to do the `Join On`, because the CSV doesn't have an ID field. I got this to work by putting a `1` in both Requestor and Supplier.