Run a SSIS package on a remote machine from a local machine with NO VS, NO SQL

Question

Hi There: I have a created a SSIS package (for example CalculateData.dtsx)  in SQlServer 2008 R2 on my local machine that has SQl 2008 R2 installed. Now I have been asked to deploy this package in a shared folder(\\dev10\Project\Test) and then I have to run this package from another local machine that has NO VS, NO SQL. The mapped directory from my local machine to the shared folder is X:\Test and the UNC path is \\dev10\Project\Test. I have to deploy this package on this folder path \\dev10\Project\Test. This folder has nothing installed in it. NO VS, NO SQL.

How should I start. I may have permisiion to install the minimum staffs in this folder path(\\dev10\Project\Test). I tried the followings:

(1) On my local machine, I have  created a .CMD batch file which is:

dtexec /FILE "C:\PDATest\InsertTable\bin\CalculateData.dtsx"  /CHECKPOINTING O
/REPORTING E. I ran this and it executes the package perfectly locally. Can I just installed this: C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe in to the shared folder \\dev10\Project\Test and can then run another .CMD to execute the package from that shared folder??? Or do I have to install full SQLServer 2008 R2 to run this DTExec.exe under this shared folder \\dev10\Project\Test.

(2) I fould the link in MSDN to run the package remotely http://msdn.microsoft.com/en-us/library/ms403355.aspx. We have to create a remote class and run it from a local program. The remote class uses Microsoft.SqlServer.ManagedDTS assembly. Can I just install this DLL to create the remote class in my shared folder \\dev10\Project\Test. Or do I have to install further staffs. Secondly, I dont know how to refer to this remote class from  a local machine that has nothing isntalled NO VB, NO SQL.

Please guide me in this case. I can not use the SQL Job agent anywhere.

Thanks  Syed

Answer 1

Hello Syed,

Under the circumstances given placing your package to a remote location is a moot point because since the only machine that has the SSIS installed is yours, you end up running the same package locally (in your environment's computing space).

---

Arthur My Blog

Answer 2

Thanks. Ok. If I install SSIS on a client machine(A) and keep the package in a  different machine that has NO VS, NO SQL(B). Can I now run the the package from A and how?

Answer 3

You should be able to, provided, the security allows, but again, this is a strange approach.

A SSIS package is not designed for interactivity.

If the user is going to run it on demand using SSMS (a bad idea) then you can probably make it a script encompassing in it the Dtexec.exe to run the package.

---

Arthur My Blog

Answer 4

Hi Syed,

I would recommend you check PsExec tool. It allows remote execution of applications.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/