Hello, I have been struggling with setting up a SQL Server Agent Job for SSIS package.
- Server A: SQL Server 2012 (MSSQL Server Management Studio)
- Server B: Java application
- Tools: Visual Studio 2019 Community (for implementing SSIS packages)
In server A, by using a domain admin account (with Windows Authentication), I could deploy my SSIS packages to "Integration Services Catalogs -> SSISDB -> XXXX folder":
They could be executed successfully by "Right Click -> Execute".
Now I would like Server B's application to connect to Server A with "SQL Server Authentication" and trigger those packages. I learned that DB accounts with "SQL Server Authentication" cannot deploy and execute packages in SSISDB. A proxy account is needed. So the follows are what I have done:
In server A, by using a domain admin account
- Create "Proxy Credential"
with ID & PW exactly the same as the domain admin's
- Map the "Proxy Credential" to the account Server B is using(sa or tempuser)
- Create a proxy for subsystem "SSIS Package Execution"
- Create a new job under "SQL Server Agent"
But then I encountered a problem: the deployed project/package(s) are not visible
Is there any permissions/authorities of I am missing?
Besides, I am still a beginner of SSMS. Is there any other ways to achieve my goal?
Thank you so much.