Skip to main content
  • EN

The goal is to write a CSV file to a SQL Server database table requiring a foreign key (FK).  This FK is not in the CSV.  In my workspace, I create this FK (BatchID) in a table using a SQLCreator, but for the life of me can't figure out how to get that (as a new attribute) into each row of the CSV.  I've tried parameters and various transformers but nothing presents the CSV + populated BatchID field.  All I get are the 25 records from the CSV and one record from the SQLCreator.

The SQLCreator that creates the BatchID uses the following SQL Code.  

declare @tbl table (id int)

insert Import_Batch (SystemName) output inserted.Import_BatchID into @tbl(id) values ('testReaderBoard')

select * from @tbl

An inspector (and database query) shows the record is created.

Maybe I need to create the BatchID in a separate workspace, because it doesn't really `exist` when the CSV reader is processed?

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.

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:

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!

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:

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.

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.

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.

Reply


Community Stats

30,112
Posts
114,305
Replies
37,745
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.