How to remotely run a DTSX package from bat file?

Question

I am trying to remotely run a DTSX package from a bat file with this command:

DTEXEC /DTS "\File System\MY_PACKAGE_NAME" /SERVER MY_SERVER_NAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V

This is working fine locally but failing remotely (I do have admin rights on the machine I am pointing to and I have SQL permissions as well) I am getting a timeout error (Login timeout expired).

Answer 1

After a bit of research it looks like it is impossible to run DTEXEC remotely (it needs to be run locally - remote execution is not supported).

To overcome this limitation the following method seems to be broadly implemented:

1. set up a SQL job to run the DTSX package
2. set up a Stored Procedure to run the job
3. use isql command line in a BAT file (remotely executed) to run the stored procedure on the relevant SQL instance (with SQL credentials and not machine credentials)

Answer 2

SSIS also exposes a web service on the hosting server. You could via code, query a machine for packages, run packages via database or file system and add / modify variables of the package programmatically via any programming platform you like.

That being said you could also set up your webservice to call your local batch file which contains your 'DETEXEC' command.

Answer 3

It's very possible, and so easily. There is no need to have a store procedure, nor SQL agent, or Web, or .NET development. I am surprised the Microsoft never suggested this:

1. Schedule a task on the SQL Server to run the DTSX package. Disable it, so it won't run until you manually execute it from a remote PC.
2. Execute the task from the PC using the command:

schtasks /run /tn MyTask [/s MySQLServer [/u [domain]user /p password]] /?

Note: if you do not like exposing the password, use the 'PSEXEC' command to execute the 'schtasks' command(download the free and powerful tool from http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx