Copy snap creating output that is multiplied by number of records

[clarrivey]

I have a pipeline that creates files based upon 490 records in an Oracle table. I create the output by doing a select on static headings that I create and union it with actual records from the table and then I sftp the file. I wanted to also be able to email the file, so snapLogic support suggested adding a Copy snap and sending one to sftp and the other to email, so I added a Copy snap right after the Oracle Execute snap that then went to a CSV Formatter snap and then to a File Writer snap to sftp. The copy also went to a CSV Formatter and then a File Writer to create a file in my directory and finally an email snap to send the file. All of this worked EXCEPT that the listing I received was 491 records (which included a header) TIMES 490!!! For whatever reason, it repeated the copy 490 times!!! Any ideas as to why???

[clarrivey]

Connect_Ed_DEVL_2021_07_01.slp (64.9 KB)

[clarrivey]

I agree - the copy SHOULD do no more than just that (but maybe it’s doing more???)!!!
I’m rather a newbie at this, so what is the slp file and how do I access/send it???

[koryknick]

@clarrivey - I’m assuming that the Oracle Insert is receiving multiple records. Further, if you look in the Dashboard, I would guess that you will see the Oracle - Execute Update SWEADTE snap is executing 490 times, and the Oracle - Execute3 is executing 490 times as well, pushing a great deal more data to the Copy and remaining downstream snaps than you are expecting.

My recommendation is that you either:

1. Split this pipeline into two parts such that the first ends after the Oracle Insert snap
   -OR-
2. Add a Tail snap after the Oracle Insert to wait for the upstream input to complete before moving on to the Oracle - Execute Update SWEADTE and other downstream snaps

[clarrivey]

Kory,
Here is the sql for the Oracle - Execute3:
select ‘ReferenceCode’||chr(09)||‘FirstName’||chr(09)||‘LastName’||chr(09)||‘Status’||chr(09)||‘Gender’||chr(09)||‘RefreshGroup’||chr(09)||‘ContactType’||chr(09)||‘HomePhone’||chr(09)||‘PrimaryPhone’||chr(09)||‘TTYPhone’||chr(09)||‘EmailAddress’||chr(09)||‘EmailAddressAlt’||chr(09)||‘SMSPhone’||chr(09)||‘Institution’
from sys.dual
union
select
rtrim(id)||chr(09)||
rtrim(fname)||chr(09)||
rtrim(lname)||chr(09)||
rtrim(ecls_code)||chr(09)||
rtrim(gender)||chr(09)||
rtrim(dept)||chr(09)||
rtrim(contact_type)||chr(09)||
rtrim(hr_phone)||chr(09)||
rtrim(primary_phone)||chr(09)||
rtrim(tty_phone)||chr(09)||
rtrim(ca_email)||chr(09)||
rtrim(primary_email)||chr(09)||
rtrim(sms_phone)||chr(09)||
‘WHEATON COLLEGE’
from sweadte
When I run it in sql (via TOAD), I get 491 output records. THAT is what I would expect to be in my Copy! I understand that Oracle - Execute Update SWEADTE snap is updating 490 records, but I don’t really understand what you mean that they are “pushing a great deal more data to the Copy…”? Please forgive my ignorance, but I thought the snaps executed in order (i.e. 2nd one didn’t run until 1st one was complete). It doesn’t make any sense to me to split the pipeline - it SHOULD be able to handle it! And I’m also not sure I fully understand the significance of a Tail snap, so I will have to look that up in the documentation.

[clarrivey]

One other quick question: in the View(s), you have things like input0 and output0. Can these stay the same throughout the pipeline, or do they need to be updated?