dtexecRemote

UPDATED WIKI: Home

dbrowne — Fri, 05 Dec 2008 02:31:02 GMT

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.

CREATED RELEASE: dtexecRemote Project Source Code (Dec 04, 2008)

Fri, 05 Dec 2008 02:26:02 GMT

UPDATED WIKI: Home

dbrowne — Thu, 30 Oct 2008 23:58:54 GMT

UPDATED WIKI: Home

dbrowne — Thu, 30 Oct 2008 23:57:27 GMT

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 would 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 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. 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.

UPDATED WIKI: Home

dbrowne — Thu, 30 Oct 2008 22:01:10 GMT

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 boxed 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 would 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 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. 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.

UPDATED WIKI: Home

dbrowne — Thu, 30 Oct 2008 21:54:43 GMT

Resource Page Description
Remote execution of SSIS packages.

Moreover SQL Server Integration Services (SSIS) is a component of SQL Server, and any server running SSIS packages must be licenced for SQL Server.

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 using ADO.NET by calling sp_start_job http://technet.microsoft.com/en-us/library/ms186757.aspx.

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 would 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 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. 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.

UPDATED WIKI: Home

dbrowne — Thu, 30 Oct 2008 21:48:56 GMT

Resource Page Description
Remote execution of SSIS packages.

Moreover SQL Server Integration Services (SSIS) is a component of SQL Server, and any server running SSIS packages must be licenced for SQL Server.

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 using ADO.NET by calling spstartjob http://technet.microsoft.com/en-us/library/ms186757.aspx.

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 would 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 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