Running an SSIS Package Programmatically on a Remote Computer
If a client (local) computer either does not have Integration Services installed or does not have access to all the resources that a package requires, you must start the package so that the package runs on the remote computer where the package is stored. You must take extra steps to ensure that the package runs on the remote computer, because a package runs on the same computer as the application that starts the package. Thus, if you run a remote package directly from an application on the local computer, the package will load and run from the local computer. Because the local computer either does not have Integration Services installed, or does not have access to all the resources that the package requires, the package will not run successfully.
Note
A client computer might not have Integration Services installed because 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.) However, if the local computer has Integration Services installed and has access to all the resources that the package requires, you can use the local computer to run a remote package. For more information, see Running an SSIS Package Programmatically on the Local Computer.
To start the package on the remote computer, you call one of the following programs:
SQL Server Agent.
Another application, component, or Web service that is running on the remote computer.
Sections in This Topic
|
Prerequisites for the Remote Computer
You have to ensure that the process that runs the package on the remote computer has the required permissions. This process requires permission not only to start the package, but to find and open all the resources that the package uses. The default permissions are frequently not sufficient, especially in a Web-based solution. A full discussion of permissions, and of authentication and authorization, is beyond the scope of this topic.
Impersonating another account to run the package is often not a successful solution. Although the package starts under the impersonated account, the additional threads that are created by the package revert to the account that is used by the process that started the package. These threads include the threads used by the data flow to load and save data. Therefore, the process account itself needs permission to most of the external resources used by the package.
Running an SSIS Package Remotely by Calling SQL Server Agent
The following sample code demonstrates how to call SQL Server Agent programmatically to run a package on the remote computer. The sample code calls the system stored procedure, sp_start_job, which in turn launches a SQL Server Agent job that is named, RunSSISPackage, and is on the remote computer. The RunSSISPackage job then runs the package on the remote computer where the job itself is running.
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
The following code samples require a reference to the System.Data assembly.
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
Running an SSIS Package Remotely by Calling a Remote Component or Service
In the previous section, the solution for running packages programmatically on a remote computer does not require any custom code on the remote computer. However, you may prefer a solution that does not rely on SQL Server Agent to run packages.
The following example provides partial code that could be used in a remote component or a Web service on the server to start Integration Services packages on the remote computer.
Sample Code
The following code samples suggest an approach for creating and testing a remote class that runs a package remotely. These samples are not complete, but provide code that could be used as part of a solution built as a remote component or as a Web service.
The samples require a reference to the Microsoft.SqlServer.ManagedDTS assembly.
Creating the Function to Run the SSIS Package Remotely
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. To support all the available loading options, this sample uses a Select Case or switch construct to select the appropriate syntax for starting the package and to concatenate the input arguments appropriately.
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)".
The LaunchPackage 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.
Important
This sample is not complete, but provides code that could be used as part of a solution built as a remote component or as a Web service. The sample does not include the code, attributes, or references that may be required to build and expose a remote component or service.
To create a remote class to run packages on the server programmatically
Open Visual Studio and create a project of the appropriate type in your preferred programming language.
The following sample code uses the name LaunchSSISPackageService for the project.
Add a reference to the Microsoft.SqlServer.ManagedDTS assembly.
Paste the sample code into the class file.
The sample shows the whole contents of the code window.
Add the code, attributes, or references that may be required to build and expose a remote component or service.
Build and test the project that includes the sample code.
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class LaunchSSISPackageService
' 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
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 Microsoft.SqlServer.Dts.Runtime;
using System.IO;
public class LaunchSSISPackageServiceCS
{
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
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();
}
}
Calling the Function to Run the SSIS Package Remotely
The following sample console application uses the remote component or service to run a package.
The LaunchPackage method of the remote class 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 remote class
In Visual Studio, create a new console application using your preferred programming language.
The sample code uses the name LaunchSSISPackageTest for the project.
Add a reference to the local proxy assembly that represents the remote component or service. If necessary, adjust the variable declaration in the sample code for the name that you assign to the 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
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)
{
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
External Resources
- Video, How to: Automate SSIS Package Execution by Using the SQL Server Agent (SQL Server Video), on technet.microsoft.com
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services