Post reply

Executing SSIS package on a remote sever

  • July 15, 2009 at 1:38 pm

    #133962

    Hi,

    I need to execute a SSIS package on a remote server running Windows 2008, with SQL server 2008 installed. I have tried the following two techniques to execute the package on the remote server, with both techniques not able to successfully execute the package:

    1. Execute the package via the web service:

    Made a simple web service that will load and execute the package via the "LoadPackage()" and the Execute() APIS. In this case, the execute() function returns the status "1", indicating failure. I have made changes in the web.config file to make impersonation equal to true, using the statement, but to no avail. I searched on the net for this problem and found that a lot many people also faces the same problem, but never found a thread with the solution for this problem. Seems that SSIS execute() function does not works in the context of the impersonated user.

    2. Remotely execute the DTExec command using the psexec utility.

    psexec \\MyServer -c DTExec /File C:\package.dtsx

    This solution works fine when I am trying to execute the DTExec on the Windows 2003 server but not with the Win2k8 server. With 2k8 server, the package execution fails with the error "CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER" error. The problem seems to be due the UAC feature of the Windows 2008. I tried turning off the UAC on the Win2k8 server, but still the package execution was getting failed. Searched the net, but didn't get any solution.

    Right now I am thinking of implementing an application on the remote server that will look for user commands from the user's machine and will then execute the package.

    My question is, is there is any other way via which I can execute the SSIS package on my remote server? Is there is any way fix through which I would be able to execute the package via the remote execution of the DTexec command using the psexec tool (procedure 2).

    Any help will be greatly appreciated as I am completly stuck on this from the last 2 days.

    Thanks

  • July 16, 2009 at 10:35 pm

    #1026000

    For number 2, move it to a different directory like c:\wk , 2008 is REALLY particular about stuff in the boot root...

    CEWII

  • July 17, 2009 at 11:48 am

    #1026360

    Have you tried to setup your package in the remote server SQL job agent? And then use script to setup the remote server job step for execution.

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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply