While working on a process to refresh the QA environment (data purge, reload data and reapply changes made over time as parts of release cycles), I ran into self-imposed requirement. I was attempting to create a process that would perform the entire process consistently. Amongst the items that I needed to include in this process to “refresh” the QA environment was the need to run a couple of SSIS packages. SSIS was not installed on this particular server where I was creating this process. I needed to have a way of executing two SSIS packages without installing SSIS on the local server.
With that need, I decided to test a remote execution of a job that had SSIS steps. The method I decided to use was to employ OSQL. Here is what I did for that.
osql -S "remoteserver\remoteinstance" -E -Q"exec msdb.dbo.sp_start_job 'Remote Job Name'"I added that line of code to a SQL Agent Job step. The job step type is Operating System (CMDExec).
This method works well for me since I had already tested the package on the remote server and knew that it worked. There are several other methods as well, this happened to be implemented more quickly for me at this time.
