Agent Job: Execute T-SQL on different server?

Posted on

Question :

While migrating SQL Server databases on updated hardware/software we separated the ETL server from the database server (referred this link).

We were moving our SSIS packages and agent jobs on ETL server. But the T-SQL steps in agent jobs need databases to be present on same server as well.

How do we proceed from here, do we create databases on the ETL server OR move SSIS packages only on ETL server and keep agent jobs on database server?

Answer :

You can trigger a SSIS job from another server, no need to move your jobs.

So you can leave your SQL Agent jobs on the database server, deploy the SSIS packages to the new ETL server and then modify your job steps to start the SSIS packages running on the ETL server from your database server. You might need to modify the ‘Run As’ part depending on how you set up your permissions in between your 2 servers.

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *