That is your problem - you are attempting to run the package from your workstation. That creates the first connection...then the package is attempting to run and it creates a second connection.
The second connection cannot be made because you do not have Kerberos setup and configured. If you had Kerberos - a certificate would be generated on your workstation that would then be passed to the second connection...which would then pass that certificate which would identify your account to be used.
I am assuming that the Oracle connection is setup with a username/password and not windows authentication. The OLEDB Connection Manager isn't connecting from the SSIS server to the Destination server - it is a connection to the Destination server only. If that one is setup to use windows authentication - then it attempts to create a connection from the device/system where the package is running to the destination.
If the package is setup in an agent job - and that package runs on ServerA - and the destination server is ServerB, then a connection will be made from ServerA to ServerB using the connection method specified (windows authentication/sql authentication).
The important piece here is the user context running the package - and where the package is being executed. Based on your information so far...I have to guess, but it sounds like you created an agent job on your local instance that references an SSIS package on the SSIS server. In that scenario - it will fail because of a double-hop issue...
To fix this - you need the following:
- A proxy account setup and configured for Integration Services on the SSIS server.
- An agent job on the SSIS instance of SQL Server - setup to run the SSIS package
- Permissions assigned in all systems where that proxy account will be connecting
*** If you do not have SQL Server installed on the SSIS Server - then you must setup Kerberos to allow for the double-hop from the system where the Agent is installed, to the SSIS Server - connecting to the destination server ***