Running an SSIS Package Programmatically on the Local Computer
With only a few lines of managed code and the correct prerequisites on the local computer, you can run a package from a custom application. The custom application can be a Windows Forms application, a console application, an ASP.NET Web form or Web service, or a Windows service.
Note
To run Integration Services packages as needed or at predetermined times, use the methods that are described in the topic, Running Packages.
Sections in This Topic
|
Prerequisites for the Local Computer
A package runs on the same computer as the application that starts the package. Thus, when an application loads a package that is stored remotely on another computer, the package runs on the local computer.
As a result, loading and running a package on the local computer has several prerequisites:
If the package contains references to resources that are external to the package, those references must be valid on the local computer. These references include connection strings, servers, files, message queues, and so forth.
Note
If the local computer does not have valid references to the resources that are external to the package, you cannot run the package on the local computer. Instead, you must ensure that the package runs on the remote computer. For more information, see Running an SSIS Package Programmatically on a Remote Computer.
You must have all the permissions that the package requires to access data sources and other resources on or from the local computer.
You can only run a package outside the development environment, Business Intelligence Development Studio, on a computer that has Integration Services installed.
Note
The terms of your SQL Server licensing may not permit you to install Integration Services on additional computers. (Integration Services is a server component, and is not redistributable to client or local computers.) If Integration Services is not and cannot be installed on the local computer, you must ensure that the package runs on the remote computer. For more information, see Running an SSIS Package Programmatically on a Remote Computer.
Creating an Application that Loads and Runs an SSIS Package on the Local Computer
In this topic, all of the methods that load and run packages programmatically require a reference to the Microsoft.SqlServer.ManagedDTS assembly. After adding this reference in a new project, import the Microsoft.SqlServer.Dts.Runtime namespace with a C# using statement or a Visual Basic Imports statement.
Methods that Load an SSIS Package
To load a package programmatically on the local computer, regardless of whether the package is stored locally or remotely, call one of the methods in the following table. All of these methods return a Package object, which you can then use to run the package or to examine the package's properties.
Storage Location |
Method to Call |
|---|---|
File |
—or— |
SSIS Package Store |
.gif "Note") Note
The methods of the Application class for working with the SSIS Package Store only support ".", localhost, or the server name for the local server. You cannot use "(local)".
|
SQL Server |
Method that Runs the SSIS Package
To run a package programmatically on the local computer, load the package as described in the previous section, and then call the Execute method of the package.
Example
Use the following procedure to develop a custom application in managed code that loads and runs a package on the local computer. Following the procedure is a sample console application that demonstrates the steps in the procedure.
To load and run a package programmatically on the local computer
Start the Visual Studio development environment, and create a new application in your preferred development language.
This example uses a console application. However, you can also run a package from a Windows Forms application, an ASP.NET Web form or Web service, or a Windows service.
On the Project menu, click Add Reference and add a reference to Microsoft.SqlServer.ManagedDTS.dll. Click OK.
Use the Visual Basic Imports statement or the C# using statement to import the Microsoft.SqlServer.Dts.Runtime namespace.
In the main routine, add the sample code that follows this procedure.
The completed console application should look like the sample code in the following section.
Note
The sample code demonstrates loading the package from the file system by using the LoadPackage method. However you can also load the package from the msdb database by calling the LoadFromSqlServer method, or from the Integration Services package store by calling the LoadFromDtsServer method.
Run the project.
The sample code executes the CalculatedColumns sample package that is installed with the SQL Server samples. The result of package execution is displayed in the console window.
Sample Code
Imports Microsoft.SqlServer.Dts.Runtime
Module Module1
Sub Main()
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
pkgLocation = _
"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" & _
"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
pkgResults = pkg.Execute()
Console.WriteLine(pkgResults.ToString())
Console.ReadKey()
End Sub
End Module
using System;
using Microsoft.SqlServer.Dts.Runtime;
namespace RunFromClientAppCS
{
class Program
{
static void Main(string[] args)
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;
pkgLocation =
@"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
@"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();
Console.WriteLine(pkgResults.ToString());
Console.ReadKey();
}
}
}
Capturing Events from a Running SSIS Package
When you run a package programmatically as shown in the sample in the previous section, you may also want to capture errors and other events that occur as the package runs. You can accomplish this by adding a class that inherits from the DefaultEvents class, and by passing a reference to that class when you load the package. Although the following example captures only the OnError event, there are many other events that the DefaultEvents class lets you capture.
To run a package on the local computer programmatically and capture package events
Follow the steps in the example in the previous section to create a project for this example.
In the main routine, add the sample code that appears after this procedure.
The completed console application should look like the following example.
Run the project.
The sample code runs the CalculatedColumns sample package that is installed with the SQL Server samples. The result of package execution is displayed in the console window, along with any errors that occur.
Sample Code
Imports Microsoft.SqlServer.Dts.Runtime
Module Module1
Sub Main()
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim eventListener As New EventListener()
pkgLocation = _
"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" & _
"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
pkg = app.LoadPackage(pkgLocation, eventListener)
pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)
Console.WriteLine(pkgResults.ToString())
Console.ReadKey()
End Sub
End Module
Class EventListener
Inherits DefaultEvents
Public Overrides Function OnError(ByVal source As Microsoft.SqlServer.Dts.Runtime.DtsObject, _
ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, _
ByVal helpFile As String, ByVal helpContext As Integer, _
ByVal idofInterfaceWithError As String) As Boolean
' Add application–specific diagnostics here.
Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description)
Return False
End Function
End Class
using System;
using Microsoft.SqlServer.Dts.Runtime;
namespace RunFromClientAppWithEventsCS
{
class MyEventListener : DefaultEvents
{
public override bool OnError(DtsObject source, int errorCode, string subComponent,
string description, string helpFile, int helpContext, string idofInterfaceWithError)
{
// Add application-specific diagnostics here.
Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
return false;
}
}
class Program
{
static void Main(string[] args)
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
pkgLocation =
@"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
@"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
Console.WriteLine(pkgResults.ToString());
Console.ReadKey();
}
}
}
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services