服务消息对象smo Oracle,Integration Services (SSIS) 连接

Integration Services (SSIS) 连接Integration Services (SSIS) Connections

03/14/2017

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory

MicrosoftMicrosoft SQL ServerSQL Server Integration ServicesIntegration Services 包使用连接来执行不同的任务以及实现 Integration ServicesIntegration Services 功能：SQL ServerSQL Server Integration ServicesIntegration Services packages use connections to perform different tasks and to implement Integration ServicesIntegration Services features:

连接到源和目标数据存储区(如文本、XML、Excel 工作簿和关系数据库)，以提取和加载数据。Connecting to source and destination data stores such as text, XML, Excel workbooks, and relational databases to extract and load data.

连接到包含引用数据的关系数据库，以执行完全查找或模糊查找。Connecting to relational databases that contain reference data to perform exact or fuzzy lookups.

连接到关系数据库，以运行 SQL 语句(例如，SELECT、DELETE 和 INSERT 命令)以及存储过程。Connecting to relational databases to run SQL statements such as SELECT, DELETE, and INSERT commands and also stored procedures.

连接到 SQL ServerSQL Server 以执行维护和传输任务，例如，备份数据库和传输登录名。Connecting to SQL ServerSQL Server to perform maintenance and transfer tasks such as backing up databases and transferring logins.

在文本和 XML 文件中以及 SQL ServerSQL Server 表中写入日志项，并将包配置写入 SQL ServerSQL Server 表。Writing log entries in text and XML files and SQL ServerSQL Server tables and package configurations to SQL ServerSQL Server tables.

连接到 SQL ServerSQL Server 以创建某些转换在执行其工作时需要的临时工作表。Connecting to SQL ServerSQL Server to create temporary work tables that some transformations require to do their work.

连接到 Analysis ServicesAnalysis Services 项目和数据库，以访问数据挖掘模型、处理多维数据集和维度，并运行 DDL 代码。Connecting to Analysis ServicesAnalysis Services projects and databases to access data mining models, process cubes and dimensions, and run DDL code.

指定现有的文件和文件夹，或创建新的文件和文件夹，以便用于 Foreach 循环枚举器和任务。Specifying existing or creating new files and folders to use with Foreach Loop enumerators and tasks.

连接到消息队列、Windows Management Instrumentation (WMI)、 SQL ServerSQL Server 管理对象 (SMO)、Web 和邮件服务器。Connecting to message queues and to Windows Management Instrumentation (WMI), SQL ServerSQL Server Management Objects (SMO), Web, and mail servers.

为了创建这些连接， Integration ServicesIntegration Services 使用了连接管理器，如下一部分所述。To make these connections, Integration ServicesIntegration Services uses connection managers, as described in the next section.

连接管理器Connection Managers

Integration ServicesIntegration Services 使用连接管理器作为连接的逻辑表示形式。uses the connection manager as a logical representation of a connection. 在设计时，可设置连接管理器的属性，以描述当包运行时 Integration ServicesIntegration Services 创建的物理连接。At design time, you set the properties of a connection manager to describe the physical connection that Integration ServicesIntegration Services creates when the package runs. 例如，连接管理器包含在设计时设置的 ConnectionString 属性；在运行时，使用该连接字符串属性中的值创建物理连接。For example, a connection manager includes the ConnectionString property that you set at design time; at run time, a physical connection is created using the value in the connection string property.

包可以使用一种连接管理器类型的多个实例，您可以在每个实例上设置这些属性。A package can use multiple instances of a connection manager type, and you can set the properties on each instance. 在运行时，一种连接管理器类型的每个实例创建具有不同属性的连接。At run time, each instance of a connection manager type creates a connection that has different attributes.

SQL ServerSQL Server Integration ServicesIntegration Services 提供了不同类型的连接管理器，从而使得包可以连接到多种数据源和服务器：Integration ServicesIntegration Services provides different types of connection managers that enable packages to connect to a variety of data sources and servers:

提供了内置连接管理器，在您安装 Integration ServicesIntegration Services时安装程序将安装这些连接管理器。There are built-in connection managers that Setup installs when you install Integration ServicesIntegration Services.

提供了可从 MicrosoftMicrosoft 网站下载的连接管理器。There are connection managers that are available for download from the MicrosoftMicrosoft website.

如果现有的连接管理器没有满足您的需求，可以创建自己的自定义连接管理器。You can create your own custom connection manager if the existing connection managers do not meet your needs.

包级别和项目级别连接管理器Package level and project level connection managers

可以在包级别或项目级别创建连接管理器。A connection manager can be created at the package level or at the project level. 在项目级别创建的连接管理器对项目中的所有包可用。The connection manager created at the project level is available all the packages in the project. 而在包级别创建的连接管理器对该特定包可用。Whereas, connection manager created at the package level is available to that specific package.

您使用在项目级别创建的连接管理器来替代数据源将连接共享到源。You use connection managers that are created at the project level in place of data sources, to share connections to sources. 要添加项目级别的连接管理器， Integration ServicesIntegration Services 项目必须使用项目部署模型。To add a connection manager at the project level, the Integration ServicesIntegration Services project must use the project deployment model. 将一个项目配置为使用此模型时， “连接管理器” 文件夹显示在 “解决方案资源管理器” 中，而 “数据源” 文件夹则从 “解决方案资源管理器” 中删除。When a project is configured to use this model, the Connection Managers folder appears in Solution Explorer , and the Data Sources folder is removed from Solution Explorer.

备注

如果您要使用包中的数据源，需要将项目转换为包部署模型。If you want to use data sources in your package, you need to convert the project to the package deployment model.

For more information about the two models, and about converting a project to the project deployment model, see Deploy Integration Services (SSIS) Projects and Packages.

内置连接管理器Built-in Connection Managers

下表列出了 SQL ServerSQL Server Integration ServicesIntegration Services 提供的连接管理器类型。The following table lists the connection manager types that SQL ServerSQL Server Integration ServicesIntegration Services provides.

类型Type

说明Description

主题Topic

ADOADO

连接到 ActiveX 数据对象 (ADO) 对象。Connects to ActiveX Data Objects (ADO) objects.

ADO.NETADO.NET

使用 .NET 提供程序连接到数据源。Connects to a data source by using a .NET provider.

CACHECACHE

从数据流或从缓存文件 (.caw) 中读取数据，并可将数据保存到缓存文件。Reads data from the data flow or from a cache file (.caw), and can save data to the cache file.

DQSDQS

连接到某一 Data Quality Services 服务器以及该服务器上的 Data Quality Services 数据库。Connects to a Data Quality Services server and a Data Quality Services database on the server.

EXCELEXCEL

连接到 Excel 工作簿文件。Connects to an Excel workbook file.

FILEFILE

连接到文件或文件夹。Connects to a file or a folder.

FLATFILEFLATFILE

连接到单个平面文件中的数据。Connect to data in a single flat file.

FTPFTP

连接到 FTP 服务器。Connect to an FTP server.

HTTPHTTP

连接到 Web 服务器。Connects to a webserver.

MSMQMSMQ

连接到消息队列。Connects to a message queue.

MSOLAP100MSOLAP100

连接到 SQL ServerSQL Server Analysis ServicesAnalysis Services 实例或 Analysis ServicesAnalysis Services 项目。Connects to an instance of SQL ServerSQL Server Analysis ServicesAnalysis Services or an Analysis ServicesAnalysis Services project.

MULTIFILEMULTIFILE

连接到多个文件和文件夹。Connects to multiple files and folders.

MULTIFLATFILEMULTIFLATFILE

连接到多个数据文件和文件夹。Connects to multiple data files and folders.

OLEDBOLEDB

使用 OLE DB 访问接口连接到数据源。Connects to a data source by using an OLE DB provider.

ODBCODBC

使用 ODBC 连接到数据源。Connects to a data source by using ODBC.

SMOServerSMOServer

连接到 SQL ServerSQL Server 管理对象 (SMO) 服务器。Connects to a SQL ServerSQL Server Management Objects (SMO) server.

SMTPSMTP

连接到 SMTP 邮件服务器。Connects to an SMTP mail server.

SQLMOBILESQLMOBILE

连接到 SQL ServerSQL Server Compact 数据库。Connects to a SQL ServerSQL Server Compact database.

WMIWMI

连接到服务器，并指定服务器上 Windows Management Instrumentation (WMI) 管理的范围。Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.

可供下载的连接管理器Connection Managers available for download

下表列出了可从 MicrosoftMicrosoft 网站下载的其他连接管理器类型。The following table lists additional types of connection manager that you can download from the MicrosoftMicrosoft website.

重要

下表中列出的连接管理器只能用于 Microsoft SQL Server 2012 EnterpriseMicrosoft SQL Server 2012 Enterprise 和 Microsoft SQL Server 2012 DeveloperMicrosoft SQL Server 2012 Developer。The connection managers listed in the following table work only with Microsoft SQL Server 2012 EnterpriseMicrosoft SQL Server 2012 Enterprise and Microsoft SQL Server 2012 DeveloperMicrosoft SQL Server 2012 Developer.

类型Type

说明Description

主题Topic

ORACLEORACLE

连接到 Oracle 服务器。Connects to an Oracle server.

Oracle 连接管理器是 MicrosoftMicrosoft Connector for Oracle by Attunity 的连接管理器组件。The Oracle connection manager is the connection manager component of the MicrosoftMicrosoft Connector for Oracle by Attunity. MicrosoftMicrosoft Connector for Oracle by Attunity 还包括源和目标。The MicrosoftMicrosoft Connector for Oracle by Attunity also includes a source and a destination.

SAPBISAPBI

连接到 SAP NetWeaver BI 7 版系统。Connects to an SAP NetWeaver BI version 7 system.

SAP BI 连接管理器是 MicrosoftMicrosoft Connector for SAP BI 的连接管理器组件。The SAP BI connection manager is the connection manager component of the MicrosoftMicrosoft Connector for SAP BI. MicrosoftMicrosoft Connector for SAP BI 还包括源和目标。The MicrosoftMicrosoft Connector for SAP BI also includes a source and a destination. For more information, see the download page, Microsoft SQL Server 2008 Feature Pack.

TERADATATERADATA

连接到 Teradata 服务器。Connects to a Teradata server.

Teradata 连接管理器是 MicrosoftMicrosoft Connector for Teradata by Attunity 的连接管理器组件。The Teradata connection manager is the connection manager component of the MicrosoftMicrosoft Connector for Teradata by Attunity. MicrosoftMicrosoft Connector for Teradata by Attunity 还包括源和目标。The MicrosoftMicrosoft Connector for Teradata by Attunity also includes a source and a destination.

自定义连接管理器Custom Connection Managers

您还可以编写自定义连接管理器。You can also write custom connection managers.

创建连接管理器Create connection managers

Integration ServicesIntegration Services 包含多种连接管理器以满足连接不同类型的服务器和数据源的任务的需要。includes a variety of connection managers to suit the needs of tasks that connect to different types of servers and data sources. 在不同类型的数据存储中提取和加载数据的数据流组件，以及将日志写入服务器、 SQL ServerSQL Server 表或文件的日志提供程序，都使用连接管理器。Connection managers are used by the data flow components that extract and load data in different types of data stores, and by the log providers that write logs to a server, SQL ServerSQL Server table, or file. 例如，具有发送邮件任务的包使用 SMTP 类型的连接管理器来连接到简单邮件传输协议 (SMTP) 服务器。For example, a package with a Send Mail task uses an SMTP connection manager type to connect to a Simple Mail Transfer Protocol (SMTP) server. 具有执行 SQL 任务的包可以使用 OLE DB 连接管理器来连接到 SQL ServerSQL Server 数据库。A package with an Execute SQL task can use an OLE DB connection manager to connect to a SQL ServerSQL Server database.

若要在创建新包时自动创建和配置连接管理器，可以使用 SQL ServerSQL Server 导入和导出向导。To automatically create and configure connection managers when you create a new package, you can use the SQL ServerSQL Server Import and Export Wizard. 该向导还有助于创建和配置使用连接管理器的源和目标。The wizard also helps you create and configure the sources and destinations that use the connection managers.

若要手动创建新的连接管理器并将其添加到现有包，可以使用在 设计器的 “控制流” 、 “数据流” 和 “事件处理程序” 选项卡上出现的 “连接管理器” SSISSSIS 区域。To manually create a new connection manager and add it to an existing package, you use the Connection Managers area that appears on the Control Flow , Data Flow , and Event Handlers tabs of SSISSSIS Designer. 从 “连接管理器” 区域，选择要创建的连接管理器的类型，并使用 SSISSSIS 设计器提供的对话框设置连接管理器的属性。From the Connection Manager area, you choose the type of connection manager to create, and then set the properties of the connection manager by using a dialog box that SSISSSIS Designer provides. 有关详细信息，请参阅本主题后面的“使用连接管理器区域”部分。For more information, see the section, "Using the Connection Managers Area," later in this topic.

将连接管理器添加到包之后，可以在任务、Foreach 循环容器、源、转换和目标中使用它。After the connection manager is added to a package, you can use it in tasks, Foreach Loop containers, sources, transformations, and destinations.

使用连接管理器区域Using the Connection Managers Area

可以在 设计器的 “控制流” 、 “数据流” 或 “事件处理程序” SSISSSIS 选项卡活动时创建连接管理器。You can create connection managers while the Control Flow , Data Flow , or Event Handlers tab of SSISSSIS Designer is active.

以下关系图显示 设计器的 “控制流” 选项卡上的 “连接管理器” SSISSSIS 区域。The following diagram shows the Connection Managers area on the Control Flow tab of SSISSSIS Designer.

用于连接管理器的 32 位和 64 位提供程序32-Bit and 64-Bit Providers for Connection Managers

连接管理器所使用的很多提供程序都有 32 位和 64 位版本。Many of the providers that connection managers use are available in 32-bit and 64-bit versions. Integration ServicesIntegration Services 设计环境是 32 位环境，设计包时您只能看到 32 位提供程序。The Integration ServicesIntegration Services design environment is a 32-bit environment and you see only 32-bit providers while you are designing a package. 因此，如果还安装了同一个提供程序的 32 位版本，则只能将连接管理器配置为使用特定的 64 位提供程序。Therefore, you can only configure a connection manager to use a specific 64-bit provider if the 32-bit version of the same provider is also installed.

在运行时，系统将使用正确的版本，即使在设计时指定了 32 位版本的提供程序也没有关系。At run time, the correct version is used, and it does not matter that you specified the 32-bit version of the provider at design time. 即使包运行在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 中，也可以运行 64 位版本的提供程序。The 64-bit version of the provider can be run even if the package is run in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT).

两种版本的提供程序都有相同的 ID。Both versions of the provider have the same ID. 若要指定 Integration ServicesIntegration Services 运行时是否使用可用的 64 位版本的提供程序，需要设置 Integration ServicesIntegration Services 项目的 Run64BitRuntime 属性。To specify whether the Integration ServicesIntegration Services runtime uses an available 64-bit version of the provider, you set the Run64BitRuntime property of the Integration ServicesIntegration Services project. 如果将 Run64BitRuntime 属性设置为 true ，运行时将发现并使用该 64 位提供程序；如果 Run64BitRuntime 为 false ，则运行时将发现并使用 32 位提供程序。If the Run64BitRuntime property is set to true , the runtime finds and uses the 64-bit provider; if Run64BitRuntime is false , the runtime finds and uses the 32-bit provider. 有关可以对 Integration ServicesIntegration Services 项目进行设置的属性的详细信息，请参阅 Integration Services (SSIS) 与 Studio 环境。For more information about properties you can set on Integration ServicesIntegration Services projects, see Integration Services &(SSIS) and Studio Environments.

添加连接管理器Add a connection manager

创建包时添加连接管理器Add a connection manager when you create a package

使用 SQL ServerSQL Server 导入和导出向导Use the SQL ServerSQL Server Import and Export Wizard

除了创建和配置连接管理器之外，该向导还有助于创建和配置使用该连接管理器的源和目标。In addition to creating and configuring a connection manager, the wizard also helps you create and configure the sources and destinations that use the connection manager.

向现有包中添加连接管理器Add a connection manager to an existing package

在项目级别添加连接管理器Add a connection manager at the project level

添加 SSIS 连接管理器对话框Add SSIS Connection Manager dialog box

使用 “添加 SSIS 连接管理器” 对话框可以选择要为包添加的连接类型。Use the Add SSIS Connection Manager dialog box to select the type of connection to add to a package.

选项Options

连接管理器类型Connection manager type

选择一个连接类型，再单击“添加”，或双击一个连接类型，使用与各连接类型相应的编辑器来指定连接属性。Select a connection type and then click Add , or double-click a connection type, to specify connection properties using the editor for each type of connection.

添加Add

使用与各连接类型相应的编辑器指定连接属性。Specify connection properties using the editor for each type of connection.

创建连接管理器属性的参数Create a parameter for a connection manager property

在“连接管理器” 区域中，右键单击要为其创建参数的连接管理器，然后单击“参数化” 。In the Connection Managers area, right-click the connection manager that you want to create a parameter for and then click Parameterize.

在 “参数化” 对话框中配置参数设置。Configure the parameter settings in the Parameterize dialog box. For more information, see Parameterize Dialog Box.

删除连接管理器Delete a connection manager

从包中删除连接管理器Delete a connection manager from a package

在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中，打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

在解决方案资源管理器中，双击该包将其打开。In Solution Explorer, double-click the package to open it.

在 SSISSSIS 设计器中，单击 “控制流” 选项卡、 “数据流” 选项卡或 “事件处理程序” 选项卡，以使 “连接管理器” 区域可用。In SSISSSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.

右键单击要删除的连接管理器，然后单击“删除” 。Right-click the connection manager that you want to delete, and then click Delete.

如果删除包元素(例如执行 SQL 任务或 OLE DB 源)使用的连接管理器，您会得到以下结果：If you delete a connection manager that a package element, such as an Execute SQL task or an OLE DB source, uses, you will experience the following results:

使用已删除的连接管理器的包元素上会出现错误图标。An error icon appears on the package element that used the deleted connection manager.

包验证失败。The package fails to validate.

包无法运行。The package cannot be run.

若要保存更新后的包，请单击 “文件” 菜单上的 “保存选定项” 。To save the updated package, click Save Selected Items on the File menu.

删除共享连接管理器(项目级别连接管理器)Delete a shared connection manager (project level connection manager)

若要删除项目级别的连接管理器，请在“解决方案资源管理器” 窗口的“连接管理器” 节点下，右键单击连接管理器，然后单击“删除” 。To delete a project-level connection manager, right-click the connection manager under Connection Managers node in the Solution Explorer window, and then click Delete. SQL Server Data ToolsSQL Server Data Tools 显示下面的警告消息：displays the following warning message:

警告

在您删除某一项目连接管理器后，使用该连接管理器的包可能会不运行。When you delete a project connection manager, packages that use the connection manager might not run. 不能撤消此操作。You cannot undo this action. 是否要删除该连接管理器?Do you want to delete the connection manager?

单击“确定”将删除该连接管理器，单击“取消”将保留它。Click OK to delete the connection manager or Cancel to keep it.

备注

您也可以从为项目中任何包打开的 “SSIS 设计器” 窗口的 “连接管理器” 选项卡中删除项目级别的连接管理器。You can also delete a project level connection manager from the Connection Manager tab of the SSIS Designer window opened for any package in the project. 为此，右键单击此选项卡中的连接管理器，然后单击“删除” 。You do so by right-clicking the connection manager in the tab and then by clicking Delete.

设置连接管理器的属性Set the Properties of a Connection Manager

所有连接管理器都可以使用 “属性” 窗口进行配置。All connection managers can be configured using the Properties window.

Integration ServicesIntegration Services 还提供了用于在 Integration ServicesIntegration Services 中修改不同类型的连接管理器的自定义对话框。also provides custom dialog boxes for modifying the different types of connection managers in Integration ServicesIntegration Services. 对话框中所包含的选项集将因连接管理器类型的不同而变化。The dialog box has a different set of options depending on the connection manager type.

使用“属性”窗口修改连接管理器Modify a connection manager using the Properties window

在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中，打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

在解决方案资源管理器中，双击该包将其打开。In Solution Explorer, double-click the package to open it.

在 SSIS 设计器中，单击 “控制流” 选项卡、 “数据流” 选项卡或 “事件处理程序” 选项卡，以使 “连接管理器” 区域可用。In SSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.

右键单击该连接管理器，再单击“属性” 。Right-click the connection manager and click Properties.

在 “属性” 窗口中，编辑属性值。In the Properties window, edit the property values. “属性” 窗口提供对在连接管理器的标准编辑器中无法配置的一些属性的访问。The Properties window provides access to some properties that are not configurable in the standard editor for a connection manager.

单击“确定”。Click OK.

若要保存更新后的包，请单击 “文件” 菜单上的 “保存选定项” 。To save the updated package, click Save Selected Items on the File menu.

使用连接管理器对话框修改连接管理器Modify a connection manager using a connection manager dialog box

相关内容Related Content