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.
Note
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 licensing might not let you install Integration Services on additional computers. Integration Services 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.
Running a Remote Package on the Remote Computer
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.
Note
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.
Sample Code
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 System;
using System.Data;
using System.Data.SqlClient;
namespace LaunchSSISPackageAgent_CS
{
class Program
{
static void Main(string[] args)
{
SqlConnection jobConnection;
SqlCommand jobCommand;
SqlParameter jobReturnValue;
SqlParameter jobParameter;
int jobResult;
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 = (Int32)jobCommand.Parameters["@RETURN_VALUE"].Value;
jobConnection.Close();
switch (jobResult)
{
case 0:
Console.WriteLine("SQL Server Agent job, RunSISSPackage, started successfully.");
break;
default:
Console.WriteLine("SQL Server Agent job, RunSISSPackage, failed to start.");
break;
}
Console.Read();
}
}
}
Back to top
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.
Important
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.
Important
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)".
Sample Code
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.
To create a Web service to run packages on the server programmatically
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
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
[WebService(Namespace = "http://dtsue/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class LaunchSSISPackageServiceCS : System.Web.Services.WebService
{
public LaunchSSISPackageServiceCS()
{
}
// 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 int LaunchPackage(string sourceType, string sourceLocation, string packageName)
{
string packagePath;
Package myPackage;
Application integrationServices = new Application();
// Combine path and file name.
packagePath = Path.Combine(sourceLocation, packageName);
switch(sourceType)
{
case "file":
// Package is stored as a file.
// Add extension if not present.
if (String.IsNullOrEmpty(Path.GetExtension(packagePath)))
{
packagePath = String.Concat(packagePath, ".dtsx");
}
if (File.Exists(packagePath))
{
myPackage = integrationServices.LoadPackage(packagePath, null);
}
else
{
throw new ApplicationException("Invalid file location: "+packagePath);
}
break;
case "sql":
// Package is stored in MSDB.
// Combine logical path and package name.
if (integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty))
{
myPackage = integrationServices.LoadFromSqlServer(packageName, "(local)", String.Empty, String.Empty, null);
}
else
{
throw new ApplicationException("Invalid package name or location: "+packagePath);
}
break;
case "dts":
// Package is managed by SSIS Package Store.
// Default logical paths are File System and MSDB.
if (integrationServices.ExistsOnDtsServer(packagePath, "."))
{
myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", null);
}
else
{
throw new ApplicationException("Invalid package name or location: "+packagePath);
}
break;
default:
throw new ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.");
}
return (Int32)myPackage.Execute();
}
}
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.
To create a console application to test the Web service
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 packageName.
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
using System;
namespace LaunchSSISPackageSvcTestCS
{
class Program
{
static void Main(string[] args)
{
LaunchSSISPackageServiceCS.LaunchSSISPackageServiceCS launchPackageService = new LaunchSSISPackageServiceCS.LaunchSSISPackageServiceCS();
int packageResult = 0;
try
{
packageResult = launchPackageService.LaunchPackage("sql", String.Empty, "SimpleTestPackage");
}
catch (Exception ex)
{
// The type of exception returned by a Web service is:
// System.Web.Services.Protocols.SoapException
Console.WriteLine("The following exception occurred: " + ex.Message);
}
Console.WriteLine(((PackageExecutionResult)packageResult).ToString());
Console.ReadKey();
}
private enum PackageExecutionResult
{
PackageSucceeded,
PackageFailed,
PackageCompleted,
PackageWasCancelled
};
}
}
Back to top
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services