Parent - Child SSIS Package Execution

Question

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.

Answer 1

The main reason is you did not provide the password for the package to decrypt the sensitive portion of it (where the credentials are).

To resolve, under the Agent job stem manually edit the command line to provide the password.

It is recommended to use the rely on server for encryption package protection level.

Answer 2

Fixed it. I to had enter the pw in the Execute Package task.