How to remotely execute an SSIS package

  • Article

There is a lot of confusion around SSIS and how and when you can execute packages, so hopefully I can help clear this up.

If you just have the SQL Server management tools on your desktop (Management Studio and Business Intelligence Development Studio aka. BIDS) then you can build a package in BIDS and run it there, but you can’t deploy to your local machine and execute there.  You need SSIS installed on a machine to execute a package.

That doesn’t mean you can’t call SSIS packages from a job server that doesn’t have SQL Server on it, however.

In order to do this you need to put your SSIS package into a job on the SQL Server box.  You don't have to schedule the job, you just need to create a job step that will execute your package.

 

Once that is done, we can call that job from any machine. We don't need the SSIS components in order to do this.

 

The code I tested goes as follows. I created a query to kick off my job and saved it in text file called query.txt - the query looks like this:

 

USE MSDB

GO

EXEC sp_start_job @job_name = 'TestPlan', @server_name = 'Pashllfixit\SS2K5'

GO

 

Then I wrote a little command to call osql.exe on the SQL Server and execute the command. I can run this from any cmd.exe on any server:

"\\pashllfixit\c$\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -E -Spashllfixit\ss2k5 -iC:\query.txt

 

And it will execute my SSIS package and we are done.

 

This will allow you to use SSIS without installing any SQL Server components on your job server.

 

Hope this helps!

 

-Reed