connecting-to-data-sources-in-a-custom-task.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords	dev_langs
Connecting to Data Sources in a Custom Task	Connecting to Data Sources in a Custom Task	chugugrace	chugu	03/04/2017	sql	integration-services	reference	ConnectionManager objects connection managers [Integration Services], external data sources data sources [Integration Services], external custom tasks [Integration Services], external data sources external data sources [Integration Services] connections [Integration Services], external data sources SSIS custom tasks, external data sources	VB CSharp

Connecting to Data Sources in a Custom Task

[!INCLUDEsqlserver-ssis]

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 xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection%2A 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 xref:Microsoft.SqlServer.Dts.Runtime.Connections collection. Tasks use the collection in their package to find the connection manager that they will use during validation and execution. The xref:Microsoft.SqlServer.Dts.Runtime.Connections collection is the first parameter to the xref:Microsoft.SqlServer.Dts.Runtime.Task.Validate%2A and xref:Microsoft.SqlServer.Dts.Runtime.Task.Execute%2A methods.

You can prevent the task from using the wrong connection manager by displaying the xref:Microsoft.SqlServer.Dts.Runtime.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 xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager objects of the appropriate type that are contained in the package.

Tasks call the xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection%2A 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 xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection%2A method to establish the connection, and does not have to be concerned with other aspects of the connection.

Example

The following sample code demonstrates validation of the xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager name in the Validate and Execute methods, and shows how to use the xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection%2A 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;  
  }  
}  

Private _connectionManagerName As String = ""  
  
Public Property ConnectionManagerName() As String  
  Get  
    Return Me._connectionManagerName  
  End Get  
  Set(ByVal Value As String)  
    Me._connectionManagerName = value  
  End Set  
End Property  
  
Public Overrides Function Validate( _  
  ByVal connections As Microsoft.SqlServer.Dts.Runtime.Connections, _  
  ByVal variableDispenser As Microsoft.SqlServer.Dts.Runtime.VariableDispenser, _  
  ByVal componentEvents As Microsoft.SqlServer.Dts.Runtime.IDTSComponentEvents, _  
  ByVal log As Microsoft.SqlServer.Dts.Runtime.IDTSLogging) _  
  As Microsoft.SqlServer.Dts.Runtime.DTSExecResult  
  
  ' If the connection manager exists, validation is successful;  
  ' otherwise fail validation.  
  Try  
    Dim cm As ConnectionManager = connections(Me._connectionManagerName)  
    Return DTSExecResult.Success  
  Catch e As System.Exception  
    componentEvents.FireError(0, "SampleTask", "Invalid connection manager.", "", 0)  
    Return DTSExecResult.Failure  
  End Try  
  
End Function  
  
Public Overrides Function Execute( _  
  ByVal connections As Microsoft.SqlServer.Dts.Runtime.Connections, _  
  ByVal variableDispenser As Microsoft.SqlServer.Dts.Runtime.VariableDispenser, _  
  ByVal componentEvents As Microsoft.SqlServer.Dts.Runtime.IDTSComponentEvents, _  
  ByVal log As Microsoft.SqlServer.Dts.Runtime.IDTSLogging, ByVal transaction As Object) _  
  As Microsoft.SqlServer.Dts.Runtime.DTSExecResult  
  
  Try  
    Dim cm As ConnectionManager = connections(Me._connectionManagerName)  
    Dim connection As Object = cm.AcquireConnection(transaction)  
    Return DTSExecResult.Success  
  Catch exception As System.Exception  
    componentEvents.FireError(0, "SampleTask", exception.Message, "", 0)  
    Return DTSExecResult.Failure  
  End Try  
  
End Function  

See Also

Integration Services (SSIS) Connections
Create Connection Managers