I have been trying to find a workable solution for this problem for last days but with no success. Please provide me any suggestion. I have checked on other forums and on MSDN for threads but they also just talk about executing webservice from SSIS Server or calling a process credentials present in code, which is not possible in current environment. OR if you think the current environment setup is not good can you give clear reasons.How PSexecs or anyother calls can be exploited in this case.
Problem Statement: Running a Remote SSIS Package from the client Web application.
Current Web Application requires to run a remote package from the Client web application which doesnot have Integration services installed on its web server.
This ssis package loads data into another SQL Server 2005. All the servers i.e. Web Server, SSIS Server, Sql Server are present independently on separate machines.
Expected Environment Calls:
Application Server (Web Server) - > SSIS Server( Execute Packages) - > SQL Server ( Database Store)
Same Windows Service account will be used to run Web application, SSIS, SQL Server. All servers are present independently and second component/server cannot be installed on same machine.
Under normal circumstances, it looks feasible to create a solution for the above problem but the current environment has couple of limitations.
With some analysis it is derived that if a remote batch file execution is possible, it will simplty run a batch file on SSIS Server to execute a package.
But It will require the user credentials to be present in the code, since we cannot take the risk of exposing the credentials in plain code.
We cannot run the package using utilities like DTEXEC directly on Web Server, since it requires SSIS components to be installed on web server.
Is there any way to run the package on SSIS Server but by invoking it from web server.?
Is there any way to encrypt credentials and call the remote batch file?
How is the credential handled and passed to the remote SSIS server, even if same service account is used on App server and SSIS Serve and also on SQL Server?
If an schedule task to run batch file( which calls DTEXEC) is created on SSIS server then can it be called from remote machine(Web Server)? If so how will the Package variables be passed to the batch file.i.e. connectionstrings;FilePathVariable etc.
And if the remote batch file execution works, how will the client application know when the package execution completed.
Other System Assumptions & out-of-scope approaches :
Application Webserver cannot have SSIS component installed.
Passing User credentials in plain code is out of scope.
SSIS server cannot have IIS installed.
SSIS server cannot have a webservice to run package locally, since it will require an IIS component to be installed on SSIS Server.
SQL Server will not use IIS or SSIS services.
SQL Server Agent job cannot be excuted on SQL server, that includes Create/Delete Job procedures.
Xp_CmdShell command cannot be used on SQL Server due to user rights.
Note: these are the restrictions present on the Servers