Loading and Running a Remote Package Programmatically
To run remote packages from a local computer that does not have Integration Services installed, start the packages so that they run on the remote computer on which Integration Services is installed. You do this by having the local computer use SQL Server Agent, a Web service, or a remote component to start the packages on the remote computer. If you try to start the remote packages directly from the local computer, the packages will load onto and try to run from the local computer. If the local computer does not have Integration Services installed, the packages will not run.
|You cannot run packages outside BI Development Studio on a client computer that does not have Integration Services installed, and the terms of your SQL Server 2005 licensing might not let you install Integration Services on additional computers. SQL Server 2005 Integration Services (SSIS) is a server component and is not redistributable to client computers.|
Alternately, you can run a remote package from a local computer that has Integration Services installed. For more information, see Loading and Running a Local Package Programmatically.
As mentioned above, there are multiple ways in which you can run a remote package on a remote server:
Use SQL Server Agent to run the remote package programmatically
Use a Web service or remote component to run the remote package programmatically
Almost all the methods that are used in this topic to load and save packages require a reference to the Microsoft.SqlServer.ManagedDTS assembly. The exception is the ADO.NET approach demonstrated in this topic for executing the sp_start_job stored procedure, which requires only a reference to System.Data. After you add the reference to the Microsoft.SqlServer.ManagedDTS assembly in a new project, import the Microsoft.SqlServer.Dts.Runtime namespace with a using or Imports statement.
Using SQL Server Agent to Run a Remote Package Programmatically on the Server
The following code sample demonstrates how to programmatically use SQL Server Agent to run a remote package on the server. The code sample calls the system stored procedure, sp_start_job, which launches a SQL Server Agent job. The job that the procedure launches is named
RunSSISPackage, and this job is on the remote computer. The
RunSSISPackage job then runs the package on the remote computer.
|The return value of the sp_start_job stored procedure indicates whether the stored procedure was able to start the SQL Server Agent job successfully. The return value does not indicate whether the package succeeded or failed.|
For information on troubleshooting packages that are run from SQL Server Agent jobs, see the Microsoft article, An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.
Imports System.Data Imports System.Data.SqlClient Module Module1 Sub Main() Dim jobConnection As SqlConnection Dim jobCommand As SqlCommand Dim jobReturnValue As SqlParameter Dim jobParameter As SqlParameter Dim jobResult As Integer jobConnection = New SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI") jobCommand = New SqlCommand("sp_start_job", jobConnection) jobCommand.CommandType = CommandType.StoredProcedure jobReturnValue = New SqlParameter("@RETURN_VALUE", SqlDbType.Int) jobReturnValue.Direction = ParameterDirection.ReturnValue jobCommand.Parameters.Add(jobReturnValue) jobParameter = New SqlParameter("@job_name", SqlDbType.VarChar) jobParameter.Direction = ParameterDirection.Input jobCommand.Parameters.Add(jobParameter) jobParameter.Value = "RunSSISPackage" jobConnection.Open() jobCommand.ExecuteNonQuery() jobResult = DirectCast(jobCommand.Parameters("@RETURN_VALUE").Value, Integer) jobConnection.Close() Select Case jobResult Case 0 Console.WriteLine("SQL Server Agent job, RunSISSPackage, started successfully.") Case Else Console.WriteLine("SQL Server Agent job, RunSISSPackage, failed to start.") End Select Console.Read() End Sub End Module
Using a Web Service or Remote Component to Run a Remote Package Programmatically
The previous solution for running packages programmatically on the server does not require any custom code on the server. However, you may prefer a solution that does not rely on SQL Server Agent to execute packages. The following example demonstrates a Web service that can be created on the server to start Integration Services packages locally, and a test application that can be used to call the Web service from a client computer. If you prefer to create a remote component instead of a Web service, you can use the same code logic with very few changes in a remote component. However a remote component may require more extensive configuration than a Web service.
|With its default settings for authentication and authorization, a Web service generally does not have sufficient permissions to access SQL Server or the file system to load and execute packages. You may have to assign appropriate permissions to the Web service by configuring its authentication and authorization settings in the web.config file and assigning database and file system permissions as appropriate. A complete discussion of Web, database, and file system permissions is beyond the scope of this topic.|
|The methods of the Application class for working with the SSIS Package Store support only ".", localhost, or the server name for the local server. You cannot use "(local)".|
The following code samples show how to create and test the Web service.
Creating the Web Service
An Integration Services package can be loaded directly from a file, directly from SQL Server, or from the SSIS Package Store, which manages package storage in both SQL Server and special file system folders. This sample supports all the available options by using a Select Case or switch construct to select the appropriate syntax for starting the package and to concatenate the input arguments appropriately. The LaunchPackage Web service method returns the result of package execution as an integer instead of a DTSExecResult value so that client computers do not require a reference to any Integration Services assemblies.
Open Visual Studio and create a Web service project in your preferred programming language. The sample code uses the name LaunchSSISPackageService for the project.
Add a reference to Microsoft.SqlServer.ManagedDTS and add an Imports or using statement to the code file for the Microsoft.SqlServer.Dts.Runtime namespace.
Paste the sample code for the LaunchPackage Web service method into the class. (The sample shows the whole contents of the code window.)
Build and test the Web service by providing a set of valid values for the input arguments of the LaunchPackage method that point to an existing package. For example, if package1.dtsx is stored on the server in C:\My Packages, pass "file" as the value of the sourceType, "C:\My Packages" as the value of sourceLocation, and "package1" (without the extension) as the value of packageName.
Imports System.Web Imports System.Web.Services Imports System.Web.Services.Protocols Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO <WebService(Namespace:="http://dtsue/")> _ <WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _ <Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _ Public Class LaunchSSISPackageService Inherits System.Web.Services.WebService ' LaunchPackage Method Parameters: ' 1. sourceType: file, sql, dts ' 2. sourceLocation: file system folder, (none), logical folder ' 3. packageName: for file system, ".dtsx" extension is appended <WebMethod()> _ Public Function LaunchPackage( _ ByVal sourceType As String, _ ByVal sourceLocation As String, _ ByVal packageName As String) As Integer 'DTSExecResult Dim packagePath As String Dim myPackage As Package Dim integrationServices As New Application ' Combine path and file name. packagePath = Path.Combine(sourceLocation, packageName) Select Case sourceType Case "file" ' Package is stored as a file. ' Add extension if not present. If String.IsNullOrEmpty(Path.GetExtension(packagePath)) Then packagePath = String.Concat(packagePath, ".dtsx") End If If File.Exists(packagePath) Then myPackage = integrationServices.LoadPackage(packagePath, Nothing) Else Throw New ApplicationException( _ "Invalid file location: " & packagePath) End If Case "sql" ' Package is stored in MSDB. ' Combine logical path and package name. If integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty) Then myPackage = integrationServices.LoadFromSqlServer( _ packageName, "(local)", String.Empty, String.Empty, Nothing) Else Throw New ApplicationException( _ "Invalid package name or location: " & packagePath) End If Case "dts" ' Package is managed by SSIS Package Store. ' Default logical paths are File System and MSDB. If integrationServices.ExistsOnDtsServer(packagePath, ".") Then myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", Nothing) Else Throw New ApplicationException( _ "Invalid package name or location: " & packagePath) End If Case Else Throw New ApplicationException( _ "Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.") End Select Return myPackage.Execute() End Function End Class
Testing the Web Service
The following sample console application uses the Web service to run a package. The LaunchPackage method of the Web service returns the result of package execution as an integer instead of a DTSExecResult value so that client computers do not require a reference to any Integration Services assemblies. The sample creates a private enumeration whose values mirror the DTSExecResult values to report the results of execution.
In Visual Studio, add a new console application, using your preferred programming language, to the same solution that contains the Web service project. The sample code uses the name LaunchSSISPackageTest for the project.
Set the new console application as the startup project in the solution.
Add a Web reference for the Web service project. If necessary, adjust the variable declaration in the sample code for the name that you assign to the Web service proxy object.
Paste the sample code for the Main routine and the private enumeration into the code. (The sample shows the whole contents of the code window.)
Edit the line of code that calls the LaunchPackage method to provide a set of valid values for the input arguments that point to an existing package. For example, if package1.dtsx is stored on the server in C:\My Packages, pass "file" as the value of
sourceType, "C:\My Packages" as the value of
sourceLocation, and "package1" (without the extension) as the value of
Module LaunchSSISPackageTest Sub Main() Dim launchPackageService As New LaunchSSISPackageService.LaunchSSISPackageService Dim packageResult As Integer Try packageResult = launchPackageService.LaunchPackage("sql", String.Empty, "SimpleTestPackage") Catch ex As Exception ' The type of exception returned by a Web service is: ' System.Web.Services.Protocols.SoapException Console.WriteLine("The following exception occurred: " & ex.Message) End Try Console.WriteLine(CType(packageResult, PackageExecutionResult).ToString) Console.ReadKey() End Sub Private Enum PackageExecutionResult PackageSucceeded PackageFailed PackageCompleted PackageWasCancelled End Enum End Module