以编程方式添加连接

内部属性

ConnectionManager 类公开一组所有连接通用的属性。但是，有时您需要访问特定连接类型特有的属性。ConnectionManager 类的 Properties 集合提供对这些属性的访问。可以使用索引器或属性名称以及 GetValue 方法从集合检索这些属性，并使用 SetValue 方法设置属性的值。设置基础连接对象属性的属性的另一种方法是：获取该对象的实际实例并直接设置其属性。若要获取基础连接，请使用连接管理器的 InnerObject 属性。下面的代码行显示了一行 C# 代码，该代码行创建一个具有基础类 ConnectionManagerAdoNetClass 的 ADO.NET 连接管理器。

ConnectionManagerAdoNetClass cmado = cm.InnerObject as ConnectionManagerAdoNet;

这可将托管连接管理器对象转换为其基础连接对象。如果您使用的是 C++，则需要调用 ConnectionManager 对象的 QueryInterface 方法，并请求基础连接对象的接口。

下表列出了随 Integration Services 提供的连接管理器，以及在 package.Connections.Add("xxx") 语句中使用的字符串。有关所有连接管理器的列表，请参阅Integration Services 连接。

下面的代码示例演示如何向 Package 的 Connections 集合添加 OLE DB 和 FILE 连接。然后，该示例还设置了 ConnectionString、Name 和 Description 属性。

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      // Create a package, and retrieve its connections.
      Package pkg = new Package();
      Connections pkgConns = pkg.Connections;

      // Add an OLE DB connection to the package, using the 
      // method defined in the AddConnection class.
      CreateConnection myOLEDBConn = new CreateConnection();
      myOLEDBConn.CreateOLEDBConnection(pkg);

      // View the new connection in the package.
      Console.WriteLine("Connection description: {0}",
         pkg.Connections["SSIS Connection Manager for OLE DB"].Description);

      // Add a second connection to the package.
      CreateConnection myFileConn = new CreateConnection();
      myFileConn.CreateFileConnection(pkg);

      // View the second connection in the package.
      Console.WriteLine("Connection description: {0}",
        pkg.Connections["SSIS Connection Manager for Files"].Description);

      Console.WriteLine();
      Console.WriteLine("Number of connections in package: {0}", pkg.Connections.Count);

      Console.Read();
    }
  }
  // <summary>
  // This class contains the definitions for multiple
  // connection managers.
  // </summary>
  public class CreateConnection
  {
    // Private data.
    private ConnectionManager ConMgr;

    // Class definition for OLE DB Provider.
    public void CreateOLEDBConnection(Package p)
    {
      ConMgr = p.Connections.Add("OLEDB");
      ConMgr.ConnectionString = "Provider=SQLOLEDB.1;" +
        "Integrated Security=SSPI;Initial Catalog=AdventureWorks2008R2;" +
        "Data Source=(local);";
      ConMgr.Name = "SSIS Connection Manager for OLE DB";
      ConMgr.Description = "OLE DB connection to the AdventureWorks2008R2 database.";
    }
    public void CreateFileConnection(Package p)
    {
      ConMgr = p.Connections.Add("File");
      ConMgr.ConnectionString = @"\\<yourserver>\<yourfolder>\books.xml";
      ConMgr.Name = "SSIS Connection Manager for Files";
      ConMgr.Description = "Flat File connection";
    }
  }

}

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

  Sub Main()

    ' Create a package, and retrieve its connections.
    Dim pkg As New Package()
    Dim pkgConns As Connections = pkg.Connections

    ' Add an OLE DB connection to the package, using the 
    ' method defined in the AddConnection class.
    Dim myOLEDBConn As New CreateConnection()
    myOLEDBConn.CreateOLEDBConnection(pkg)

    ' View the new connection in the package.
    Console.WriteLine("Connection description: {0}", _
      pkg.Connections("SSIS Connection Manager for OLE DB").Description)

    ' Add a second connection to the package.
    Dim myFileConn As New CreateConnection()
    myFileConn.CreateFileConnection(pkg)

    ' View the second connection in the package.
    Console.WriteLine("Connection description: {0}", _
      pkg.Connections("SSIS Connection Manager for Files").Description)

    Console.WriteLine()
    Console.WriteLine("Number of connections in package: {0}", pkg.Connections.Count)

    Console.Read()

  End Sub

End Module

' This class contains the definitions for multiple
' connection managers.

Public Class CreateConnection
  ' Private data.
  Private ConMgr As ConnectionManager

  ' Class definition for OLE DB provider.
  Public Sub CreateOLEDBConnection(ByVal p As Package)
    ConMgr = p.Connections.Add("OLEDB")
    ConMgr.ConnectionString = "Provider=SQLOLEDB.1;" & _
      "Integrated Security=SSPI;Initial Catalog=AdventureWorks2008R2;" & _
      "Data Source=(local);"
    ConMgr.Name = "SSIS Connection Manager for OLE DB"
    ConMgr.Description = "OLE DB connection to the AdventureWorks2008R2 database."
  End Sub

  Public Sub CreateFileConnection(ByVal p As Package)
    ConMgr = p.Connections.Add("File")
    ConMgr.ConnectionString = "\\<yourserver>\<yourfolder>\books.xml"
    ConMgr.Name = "SSIS Connection Manager for Files"
    ConMgr.Description = "Flat File connection"
  End Sub

End Class

示例输出：

Connection description: OLE DB connection to the AdventureWorks2008R2 database.

Connection description: OLE DB connection to the AdventureWorks2008R2 database.

Number of connections in package: 2

外部资源

carlprothman.net 上的技术文章连接字符串。

请参阅

任务

概念