Search Wiki:
Remote execution of SSIS packages.

From time to time people ask me hot to run an SSIS package remotely. SQL Server Integration Services (SSIS) is a component of SQL Server, and any server running SSIS packages must be licenced for SQL Server. So if you'd like to kick off an SSIS package from a web page or some other kind of application, you generally can't just invoke the SSIS runtime directly or shell out to dtexec.exe. Even if you could, the SSIS runtime is already installed on your SQL Servers, and running SSIS packages on application boxes often requires pushing data needlessly across the network.

I always tell people that the basic method for remote execution of SSIS package is to deploy the package to a SQL Server, create a SQL Agent job to run the package and remotely kick off the SQL Agent job by calling sp_start_job http://technet.microsoft.com/en-us/library/ms186757.aspx, which can be done from any programming language, or on the command line with SqlCmd.exe.

But this isn't a very good solution if you need to pass parameters to the package, dynamically choose the package at runtime, change the pakage configuration, wait for the package execution to complete or see the error output of the package. In these cases it you can just run dtexec.exe http://msdn.microsoft.com/en-us/library/ms162810.aspx on the commandline. But dtexec is part of SSIS and can only be run on the SQL Server box, not on an application server.

This sample shows how to achieve the functionality of dtexec from a remote box. It's packaged as a command line program that takes exactly the same command line parameters as dtexec, but instead of running the package locally, it connects to SQL Server Agent on a remote SQL Server, creates a temporary Job to run the requested package with the requested command line, executes the Job, waits for the Job to finish, captures the job history and deletes the Job.

This code also shows how to create and execute a SQL Agent Job through code using SQL Server Management Objects (SMO). Often in an application it will be easier to just paste this SMO code into your project and interact with SQL Agent directly from your own code instead of shelling out to dtexecRemote.exe.

To build and run this sample, you will need to install SMO, which is available for download as part of the Microsoft SQL Server 2008 Feature Pack http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en. This sample is built against the SQL 2008 version of SMO which should work just fine. Also if you change the references in the project it shold run against the older SQL 2005 version of SMO too. It's just interacting with SQL Agent, which didn't really change between 2005 and 2008.

This is a source code sample, and the easiest way to build it is using Visual Studio 2008. However, like all Visual Studio projects, you can also build this project using msbuild from the command line. Msbuild.exe is part of the .NET framework, available for download here: http://msdn.microsoft.com/en-us/netframework/default.aspx.

Last edited Dec 5 2008 at 2:31 AM  by dbrowne, version 7
Comments
vdeineko wrote  Mar 26 2009 at 7:42 PM  
How to remotely execute MS SQL SSIS package

I find my solution the easiest of all. It does not require neither SQL Agent job, nor SMO, or any Visual Studio development , just one-line primitive scripting.

1. Say, you created a local SSIS package, called MySSIS.dtsx in the directory 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:\MyDir\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 execution 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

(More on the ‘schtasks’ options type schtasks /? at the command prompt.)

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, from where you would execute the remote SSIS. Say, you installed it in the c:\pstools directory.

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 or a MS Access Macro, and just 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. The solution seemlessly bypasses the SQL licensing restriction.

Please let me know what you think.

Daver555 wrote  Jul 26 2011 at 11:42 PM  
Getting an SSIS package to run using WCF and a package launcher assembly was proving to be a travesty. Thank you most earnestly for a solution that actually worked.

gorschkow wrote  Jul 4 2012 at 4:53 PM  
My goodness, I can only agree to Daver555...! Getting SSIS package with an Excel(!) based import task to work with DTEXEC & PSEXEC from a client seems almost impossible due to security permissions. But doing it with a scheduled task works perfectly! Big Thumbs up for this nice&easy solution!!
(For all those googling around with a similar problem my search tags: "the acquireconnection method call to the connection manager excel" failed 80004005

Updating...
Page view tracker