THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Remote Execution of SSIS Packages

Having the need to execute a package on a remote server, I discovered  that DtExec has no option to launch the package remotely (something that everybody apart me already knew…).

So… time to write some code. I wrote a WCF Service that executes a package, hosted it into a Windows Service (it can be hosted in IIS too, I just like services more than I like the web interfaces) and developed a test program that uses WCF to communicate with the server to execute the package. All this stuff has been indeed really easy to write (and this induces me to argue why Microsoft did not provide such a tool with standard SQL Server installation).

Using the WCF callback methods I succeeded in returning to the caller full details of log and events produced by the package, so that I can run a package remotely and still monitor what happens on the server side from the client. Moreover, since I needed to call the package from inside an application, I implemented a nice progressbar that shows to the user the progress of the package, by inspecting the package events, so that he knows if he can have a coffee during processing or not.

At the end, the solution is a very nice example of what can be done using WCF as a communication media and, since I don’t think that I’m the only one having such a need, I am sharing the sources with the web. If you are interested in getting the code, with some basic documentation, follow the link to the source code and have fun.

BTW: the test application can be used without the need to understand all the code and is basically a DTEXEC replacement that runs packages remotely… something that can be useful to anybody developing SSIS code.

Published Wednesday, December 02, 2009 4:28 PM by AlbertoFerrari

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

csm said:

I think there's another options:

  - you can create a job on the remote server that execute a SSIS package and execute with linked_server.msdb..sp_startjob

  - or connect via sqlcmd and execute this job.

  - or use sqlcmd + xp_cmdshell + dtexec

December 2, 2009 12:02 PM
 

AlbertoFerrari said:

Csm,

Yep, you have plenty of options to perform the same task (even if no one is really easy) but I wanted a simple way to receive all the log and events from the executed package, to track the progress of it and show coloured progress bars, which users seems to love.

Moreover, the client running the software (which in turn is an Excel Add-in, to make things a bit more complicated) does not have SQL Server nor any fancy software installed, just plain vanilla Windows with .NET libraries (and VSTOR, of course).

December 2, 2009 12:11 PM
 

Harrison D said:

I'm not sure I understand the relevance of this post. Basically you need to execute a package on a remote server. Doesn't DTEXEC provide you with an option /Server already?

See this link: http://msdn.microsoft.com/en-us/library/ms162810.aspx

December 2, 2009 2:17 PM
 

AlbertoFerrari said:

Harrison,

that option indicates the server instance from where to load the package when it is stored in the SQL Server repository. Nevertheless, the package will be loaded by the computer running DTEXEC and executed by itself.

I fell into the same misunderstanding, that is the reason for which I believed DTEXEC would be able to execute a package remotely. Unfortunately, this is not the case.

December 2, 2009 3:21 PM
 

Drewsky said:

Why not use sysinternals PSexec?

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Thx,

Drew

December 3, 2009 12:15 AM
 

AlbertoFerrari said:

Drewsky,

PsExec whould have been an interesting choice, I didn't know about it. Thanks for the tip.

December 3, 2009 4:13 AM
 

DanielA said:

Interesting post!

I think this could be a nice way of executing packages in "ssis custom load balancing",i.e. you have some very complex, resource consuming logic stored in ssis packages stored on multiple servers, then basing on resource consumption (e.g. using WMI) you decide on which server your package should be executed.

Daniel

December 9, 2009 5:55 PM
 

Sean D said:

Excellent. This is exactly what I was looking for. I'm surprised that Microsoft haven't provided a better facility for executing SSIS on the (remote) server. A lot of people get confused as to where the actual package is running, and no real-world organisation is going to have BIDS (or even SSIS runtimes) installed on users machines to allow such a fundamental operation.

Many thanks!

March 19, 2010 7:49 AM

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement