Thursday, October 16, 2008

[SSIS] How to remotely run a DTSX package from bat file?

Short answer (or at least the conclusion I came to after a fair amount of research  - and plenty of hair pulling): you can't .

Long Answer: I started from this cmd, which works like charm locally:


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


If you try to use the same cmd remotely it will miserably fail with some nasty timeout.

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

To overcome this limitation the following method seems to be broadly implemented:
  • set up a SQL job to run the DTSX package
  • set up a Stored Procedure to run the job
  • 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)
Might work - but kinda sucks.

No comments: