SSIS Package Parent - Child Execution

sydney 1 Reputation point
2021-02-18T21:04:55.98+00:00

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,092 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,776 Reputation points
    2021-02-19T07:16:03.74+00:00

    Hi @sydney ,

    If I run parent.dtsx via a sql agent job, I get an error saying "ORA-01005: null password given; logon denied".

    May I know if the user that to execute the parent.dtsx has full permission to connect to the oracle connection?

    May I know if you try to run parent.dtsx in SSDT?

    Please try to deploy the ssis packages to SSISDB Catalog and then run the packages in Sql Agent Job from ssisdb catalog.

    Please refer to ORA-01005: null password given; logon denied" Error when trying.

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.