November 4, 2008 at 9:00 am
Here is the problem.
I've two servers SQL and application server. Users have to run package from application server, since they have no access to SQL.
I've created *.bat file that executes this package:
DTexec /SQ "SSIS_package" /Ser "sql_server" /U "user" /P "password"
I can run this package in SQL server without any problems, but when I try to run this command from application server I get following message:
Error: 2008-11-04 18:31:41.69
Code: 0xC0010025
Source: SSIS_package
Description: The package cannot execute because it contains tasks that failed to load.
End Error
I'm wondering if this scenario is even possible!?
November 4, 2008 at 9:45 am
To execute an SSIS package, you must be on an SSIS Server. So, unless you have done a full install of the SSIS Server service on your application server, you cannot run the package from the DTExec command line there. This is mostly a licensing thing. SSIS is not really a client/server application - you do not invoke the SSIS package on the server it is stored on.
You have a couple of options.
Use the SQL Agent to start a package. This would allow you to start a package with sp_start_job and it will execute on the server that the SQL Agent is running on. Unfortuantely, this will not allow concurrent executions of the package unless you are very creative in dynamically adding jobs.
The other option is to write a service (or web service) that runs on the SSIS server and allows you to start your package remotely. This is pretty easy and usually the best solution.
November 4, 2008 at 10:08 am
Michael Earl (11/4/2008)
The other option is to write a service (or web service) that runs on the SSIS server and allows you to start your package remotely. This is pretty easy and usually the best solution.
Thanks for reply! 🙂
Do you meant to write some PHP script, to execute this package?
Could explain more in details or give some simple example?
November 4, 2008 at 10:19 am
MS gives some sample code (search google) for executing an SSIS package programatically. Wrap this in a web service and run the web service on the SSIS server. Then you will be able to call the web service from a remote machine to start the package.
March 26, 2009 at 1:20 pm
Maybe by now you already found a solution, yet I will post mine here for others.
I find my solution the easiest of all. It does not require neither VB development nor SQL Server installation on the remote PC.
1. Say, you created a local SSIS package , called MySSIS.dtsx in the c:\MyDir, located on the SQL Server, named MySQLServer.
2. Now, create a simple batch file (or a vb script) on the server allowing you to execute the 'dtexec' command. Something like this:
dtexec /FILE "C:\MyDir\MySSIS.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
Full 'dtexec' command options are here :
http://technet.microsoft.com/en-us/library/ms162810.aspx
Save the batch file, say, as 'c:\MyDirectory\MyBatch.bat'
3. Create a Scheduled Task to execute the batch file. Name it, say, as 'MyTask.' You can set the task to execute once, and even with the past time. This way it really will not execute, unless you manually execute it.
Test the manual task execution by running the following command from the command prompt:
>schtasks /run /tn MyTask
Well, if everything is correct, it will work, so let's do the next step.
4. Download the PSEXEC tool from here:
http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx
It's free, and it will allow you to remotely execute programs.
Install it on your PC, which would execute the remote SSIS. Say, you installed it in the c:\pstools.
5. Lastly, open the command prompt and type
>"c:\pstools\psexec" \\MySQLServer schtasks /run /tn MyTask
6. Of course, you can encapsulate the last command into a vbs script or a batch, and just double-click to execute it.
Resuming the above: You need to execute PSEXEC command to execute the remote SCHEDULED task which executes the local SSIS package. You'd ask why not execute the remote SSIS package directly with the PSEXEC? Because it won't work. If you curios about the reasons, dig in the google.
As the infamous Nick Burns from the SNL was saying: "Was that so hard?!"
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply