ESP Workload Automation

 View Only
  • 1.  Schedule job with password-protected SSIS package

    Posted Dec 10, 2024 04:16 PM

    I am a SQL Server DBA who is helping some team members move SQL Agent jobs to ESP. One of these jobs includes a SSIS package that is password-protected. The package works fine in SQL Agent, but I am being told by someone that the package cannot be run in ESP because of the password-protection.

    Due to security reasons, we require the password protection on the package.

    Does anyone know how we can resolve this situation without removing the protection on the SSIS package?



  • 2.  RE: Schedule job with password-protected SSIS package

    Broadcom Employee
    Posted Dec 16, 2024 12:31 AM

    Hi Brandie,

    May I know if you are talking about different credentials than the SQL Server credentials to access the SSIS package?

    Thanks

    Venkat D (Broadcom Automation Engineering)




  • 3.  RE: Schedule job with password-protected SSIS package

    Posted Dec 17, 2024 11:32 AM

    Yes, the package is password protected separately from SQL Server credentials. One has to have the password to get into the package before being able to run it.

    SQL Server Agent allows this behavior, with a switch added to the command line that includes the password.




  • 4.  RE: Schedule job with password-protected SSIS package

    Broadcom Employee
    Posted Dec 17, 2024 11:42 PM

    Hi Brandie,

    Adding switch to the command line is already part of SQL Agent job itself.

    That means when you execute the job from SQL Agent in SQL Studio, it does not prompt for the password right?

    SQL Agent job is self contained, it does not prompt anything from the user.

    If so we can just run it from ESP by specifying the SQL Agent jobname or job id.

    I am able to run an SSIS package job successfully.

    Please correct me if I am missing something here.

    Thanks

    Venkat D,

    Broadcom Engineering




  • 5.  RE: Schedule job with password-protected SSIS package

    Posted Dec 18, 2024 07:20 AM

    We are not scheduling a SQL Server Agent job. We are creating an ESP job that runs an SSIS package which is password-protected. I know how the behavior works in SQL Server Agent, which is why I referenced it.

    Due to security issues in the way SQL Server Agent works, the app team cannot use it for this work because SQL Server would restrict editing of the job and running of the job to the job owner. Hence they are planning to use ESP where the security will allow the team to work on & with the jobs when the job owner is out of office.




  • 6.  RE: Schedule job with password-protected SSIS package

    Broadcom Employee
    Posted Dec 21, 2024 10:45 PM

    Hi Brandie,

    Thanks for letting us know that you are not scheduling the ESP job using the SQL Server Agent.

    Are you using ESP batch job/command job that makes use of Microsoft SQL Server's dtexec executable to run the SSIS package or running the SSIS package through the database stored procedure etc? if not, please give us more details. 

    If possible, please provide the ESP job definition that is failing to execute SSIS package.

    Thanks

    Venkat D (Broadcom Engineering)