I have two SSIS packages named Parent.dtsx and Child.dtsx. For simplicity, the child package simply
inserts a row into an oracle table and this .dtsx has EncryptSensitiveWithPassword for the Oracle connection.
The parent package has only one task which is Execute Package Task which calls the Child.dtsx.
The parent package has ReferenceType = ExternalReference with the connection pointing to SQL Server that
contains both parent.dtsx and child.dtsx.
Child.dtsx runs fine in Visual Studio. I get the row inserted. If I run parent.dtsx via a sql agent job, I get an error saying "ORA-01005: null password given; logon denied" However, If I logon to Integration Service and manually do a "Child -> Run Package", it works fine. Of course, I get the pop up asking for the password to the package because it's encrypted.
Why can't I run via the SQL Agent to execute parent.dtsx? If I change the agent job step to run child.dtsx, then it works.