Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Adit001
Starting Member
2 Posts |
 Posted - 2009-07-15 : 15:40:14
|
| 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.dtsxThis 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 completely stuck on this from the last 2 days.Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-15 : 15:47:43
|
What are the surrounding conditions?I have had a similar problem but that whas something like export package from 2005 import in 2008 and then problems with the connection managers.The solution was to open the package in VS and disable the use of config files and give the requested credentials in the job step.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Adit001
Starting Member
2 Posts |
Posted - 2009-07-15 : 16:07:49
|
| Hi Webfred,Thanks for the reply. I am not sure of what you mean by "surrounding conditions". My package does not uses any config file.Btw, did you went for the remote package execution using the dtexec command or via some web service?Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-15 : 16:35:59
|
"surrounding conditions" - that's my poor english.I wanted to know the circumstances - maybe that is a more clear word for this...In my case it wasn't remote 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-16 : 14:21:00
|
quote:
Originally posted by Adit001
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.dtsxThis 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 completely stuck on this from the last 2 days.Thanks
is this for manual running of package whenever required or is it for periodic task? running pacakge daily,weekly,..? if latter, it would be better to configure a job in remote server to execute this.Also if you've client tools installed on your pc and have access to integration services engine of remote server you can browse for package inside msdb folder of integration server and right click and execute package. |
|
|
|
|
|
|
|
|
Executing SSIS package on remote server