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.
Original Message:
Sent: Dec 18, 2024 07:20 AM
From: Brandie Tarvin
Subject: Schedule job with password-protected SSIS package
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.
Original Message:
Sent: Dec 17, 2024 11:42 PM
From: Venkateswarlu Dondapati
Subject: Schedule job with password-protected SSIS package
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
Original Message:
Sent: Dec 17, 2024 11:32 AM
From: Brandie Tarvin
Subject: Schedule job with password-protected SSIS package
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.
Original Message:
Sent: Dec 16, 2024 12:30 AM
From: Venkateswarlu Dondapati
Subject: Schedule job with password-protected SSIS package
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)
Original Message:
Sent: Dec 10, 2024 02:35 PM
From: Brandie Tarvin
Subject: Schedule job with password-protected SSIS package
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?