Best Approach for Handling CSV Import with Truncate in Pipeline: Ensuring Correct Execution Order

[SL12345]

I want to ask:
I’m attaching a screenshot of the pipeline that should:

• Read a CSV file, truncate SQL tables, and after the successful truncate, insert all the records from the CSV into the truncated table.
  It seems that the pipeline doesn't work correctly.

It reads the CSV file (100k rows), then does the truncate, and inserts new rows.

Shouldn’t there be a GATE or sequence before the truncate step? I mean, to make sure that the truncate happens only once after all the rows are loaded?

What’s the best approach?

Thank you.

[koryknick · Accepted solution]

@SL12345 - you need to remember that SnapLogic is a streaming platform, meaning that by placing the truncate where you did, it is performing the truncate for every record in the file before inserting each record, which doesn't make sense.  You can move the truncate before the File Reader so that the sequence is truncate, read, parse, insert. 

If you want to ensure that the file exists before truncating the target, you can put a Directory Browser snap at the beginning of the pipeline so it can stop successfully if the file doesn't exist and won't truncate the table in that case.  If it does exist, it will continue along with the truncate, file read, parse, and load.

Hope this helps!

[SL12345]

Thank you very much, it works as i want 🙂