Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Connecting to Data Sources in a Custom Task

Tasks connect to external data sources to retrieve or save data by using a connection manager. At design time, a connection manager represents a logical connection, and describes key information such as the server name and any authentication properties. At run time, tasks call the AcquireConnection method of the connection manager to establish the physical connection to the data source.

Because a package can contain many tasks, each of which may have connections to different data sources, the package tracks all the connection managers in a collection, the Connections collection. Tasks use the collection in their package to find the connection manager that they will use during validation and execution. The Connections collection is the first parameter to the Validate and Execute methods.

You can prevent the task from using the wrong connection manager by displaying the ConnectionManager objects from the collection to the user, by using a dialog box or drop-down list in the graphical user interface. This gives the user a way to select from among only those ConnectionManager objects of the appropriate type that are contained in the package.

Tasks call the AcquireConnection method to establish the physical connection to the data source. The method returns the underlying connection object that can then be used by the task. Because the connection manager isolates the implementation details of the underlying connection object from the task, the task only has to call the AcquireConnection method to establish the connection, and does not have to be concerned with other aspects of the connection.

The following sample code demonstrates validation of the ConnectionManager name in the Validate and Execute methods, and shows how to use the AcquireConnection method to establish the physical connection in the Execute method.

    private string connectionManagerName = "";

    public string ConnectionManagerName
    {
      get { return this.connectionManagerName; }
      set { this.connectionManagerName = value; }
    }

    public override DTSExecResult Validate(
      Connections connections, VariableDispenser variableDispenser,
      IDTSComponentEvents componentEvents, IDTSLogging log)
    {
      // If the connection manager exists, validation is successful;
      // otherwise, fail validation.
      try
      {
        ConnectionManager cm = connections[this.connectionManagerName];
        return DTSExecResult.Success;
      }
      catch (System.Exception e)
      {
        componentEvents.FireError(0, "SampleTask", "Invalid connection manager.", "", 0);
        return DTSExecResult.Failure;
      }
    }

    public override DTSExecResult Execute(Connections connections, 
      VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, 
      IDTSLogging log, object transaction)
    {
      try
      {
        ConnectionManager cm = connections[this.connectionManagerName];
        object connection = cm.AcquireConnection(transaction);
        return DTSExecResult.Success;
      }
      catch (System.Exception exception)
      {
        componentEvents.FireError(0, "SampleTask", exception.Message, "", 0);
        return DTSExecResult.Failure;
      }
    }
Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.