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

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

Answer 2

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)