连接到 ODBC 数据源(SQL Server 导入和导出向导)Connect to an ODBC Data Source (SQL Server Import and Export Wizard)

适用于:是 SQL Server 是 Azure 数据工厂中的 SSIS Integration Runtime 是Azure Synapse Analytics (SQL DW) APPLIES TO: yesSQL Server yesSSIS Integration Runtime in Azure Data Factory yesAzure Synapse Analytics (SQL DW)

本主题介绍如何从 SQL Server 导入和导出向导的“选择数据源”页或“选择目标”页连接到 ODBC 数据源 。This topic shows you how to connect to an ODBC data source from the Choose a Data Source or Choose a Destination page of the SQL Server Import and Export Wizard.

可能需要从 Microsoft 或第三方下载所需的 ODBC 驱动程序。You may have to download the ODBC driver you need from Microsoft or from a third party.

可能还需要查找必须提供的连接信息。You may also have to look up the required connection info that you have to provide. 第三方站点 - The Connection Strings Reference(连接字符串参考) - 包含示例连接字符串、关于数据提供程序的详细信息及它们需要的连接信息。This third-party site - The Connection Strings Reference - contains sample connection strings and more info about data providers and the connection info they require.

确保已安装所需驱动程序Make sure the driver you want is installed

  1. 在开始菜单或控制面板中搜索或浏览到“ODBC 数据源 (64 位)”小程序 。Search for or browse to the ODBC Data Sources (64-bit) applet in the Start Menu or Control Panel. 如果只有 32 位驱动程序,或了解必须使用 32 位驱动程序,请改为搜索或浏览到“ODBC 数据源(32 位)” 。If you only have a 32-bit driver, or you know that you have to use a 32-bit driver, search for or browse to ODBC Data Sources (32-bit) instead.

  2. 启动小程序。Launch the applet. 此时会打开“ODBC 数据源管理器”窗口 。The ODBC Data Source Administrator window opens.

  3. 在“驱动程序”选项卡上,可找到计算机上安装的所有 OBDC 驱动程序的列表 。On the Drivers tab, you can find a list of all the ODBC drivers installed on your computer. (部分驱动程序的名称可能以多个语言列出。)(The names of some of the drivers may be listed in multiple languages.)

    下面是已安装的 64 位驱动程序的示例列表。Here's an example of the list of installed 64-bit drivers.

    已安装的 64 位 ODBC 驱动程序

提示

如果知晓安装了驱动程序但在 64 位小程序中未看到它,请改为在 32 位小程序中查找。If you know that your driver's installed and you don't see it in the 64-bit applet, look in the 32-bit applet instead. 通过这种方法可同时获知是需要运行 64 位还是 32 位 SQL Server 导入和导出向导。This also tells you whether you have to run the 64-bit or 32-bit SQL Server Import and Export Wizard.

若要使用 64 位版本的 SQL Server 导入和导出向导,必须安装 SQL Server。To use the 64-bit version of the SQL Server Import and Export Wizard, you have to install SQL Server. SQL Server Data Tools (SSDT) 和 SQL Server Management Studio (SSMS) 是 32 位应用程序且仅安装 32 位文件,包括 32 位版本的向导。SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) are 32-bit applications and only install 32-bit files, including the 32-bit version of the wizard.

步骤 1 - 选择数据源Step 1 - Select the data source

计算机上安装的 ODBC 驱动程序不在数据源下拉列表中列出。The ODBC drivers installed on your computer aren't listed in the drop-down list of data sources. 要使用 ODBC 驱动程序进行连接,请首先在向导的“选择数据源”页或“选择目标”页上选择“用于 ODBC 的 .NET Framework 数据提供程序”作为数据源 。To connect with an ODBC driver, start by selecting the .NET Framework Data Provider for ODBC as the data source on the Choose a Data Source or Choose a Destination page of the wizard. 此提供程序充当 ODBC 驱动程序的包装器。This provider acts as a wrapper around the ODBC driver.

下面是选择用于 ODBC 的 .NET Framework 数据提供程序后随即显示的常规屏幕。Here's the generic screen that you see immediately after selecting the .NET Framework Data Provider for ODBC.

先使用 ODBC 连接到 SQL

步骤 2 - 提供连接信息Step 2 - Provide the connection info

下一步是为 ODBC 驱动程序和数据源提供连接信息。The next step is to provide the connection info for your ODBC driver and your data source. 您有两种选择:You have two options.

  1. 提供已经存在的 DSN(数据源名称),或使用“ODBC 数据源管理器”小程序创建的 DSN 。Provide a DSN (data source name) that already exists or that you create with the ODBC Data Source Administrator applet. DSN 是连接 ODBC 数据源时所需的设置(已保存)的集合。A DSN is the saved collection of settings required to connect to an ODBC data source.

    如果已知 DSN 名称或已知如何创建新的 DSN,可跳过本页的其余部分。If you already know the DSN name, or know how to create a new DSN now, you can skip the rest of this page. 在“选择数据源”页或“选择目标”页上的“DSN”字段中输入 DSN 名称,然后继续执行向导的下一步 。Enter the DSN name in the Dsn field on the Choose a Data Source or Choose a Destination page, then continue to the next step of the wizard.

    提供 DSN Provide a DSN

  2. 提供一个字符串,可在计算机上使用“ODBC 数据源管理器”小程序联机查看或创建和测试该字符串 。Provide a connection string, which you can look up online, or create and test on your computer with the ODBC Data Source Administrator applet.

    如果已有连接字符串或知道如何创建,可跳过本页的其余部分。If you already have the connection string or know how to create it, you can skip the rest of this page. 在“选择数据源”页或“选择目标”页上的“ConnectionString”字段中输入连接字符串,然后继续执行向导的下一步 。Enter the connection string in the ConnectionString field on the Choose a Data Source or Choose a Destination page, then continue to the next step of the wizard.

    提供一个连接字符串 Provide a connection string

如果提供连接字符串,“选择数据源”或“选择目标”页将显示向导连接到数据源要使用的所有连接信息,例如服务器、数据库名称和身份验证方法 。If you provide a connection string, the Choose a Data Source or Choose a Destination page displays all the connection info that the wizard is going to use to connect to your data source, such as server and database name and authentication method. 如果提供 DSN,此信息不可见。If you provide a DSN, this information isn't visible.

选项 1 - 提供 DSN

Option 1 - Provide a DSN 如果要在连接信息中提供 DSN(数据源名称),使用“ODBC 数据源管理器”小程序,查找现有 DSN 的名称或创建一个新 DSN 。If you want to provide the connection information with a DSN (data source name), use the ODBC Data Source Administrator applet to find the name of the existing DSN, or to create a new DSN.

  1. 在开始菜单或控制面板中搜索或浏览到“ODBC 数据源 (64 位)”小程序 。Search for or browse to the ODBC Data Sources (64-bit) applet in the Start Menu or Control Panel. 如果只有 32 位驱动程序,或必须使用 32 位驱动程序,请改为搜索或浏览到“ODBC 数据源(32 位)” 。If you only have a 32-bit driver, or have to use a 32-bit driver, search for or browse to ODBC Data Sources (32-bit) instead.

  2. 启动小程序。Launch the applet. 此时会打开“ODBC 数据源管理器”窗口 。The ODBC Data Source Administrator window opens. 下面是小程序的外观。Here's what the applet looks like.

    ODBC 管理器控制面板小程序

  3. 如果要对数据源使用现有 DSN,可以使用“用户 DSN”、“系统 DSN”或“文件 DSN”选项卡上显示的任何 DSN 。检查名称,然后返回向导,在“选择数据源”页或“选择目标”页上的“DSN”字段中输入该名称 。If you want to use an existing DSN for your data source, you can use any DSN that you see on the User DSN, System DSN, or File DSN tab. Check the name, then go back to the wizard and enter it in the Dsn field on the Choose a Data Source or Choose a Destination page. 跳过本页的其余部分并继续执行向导的下一步。Skip the rest of this page and continue to the next step of the wizard.

  4. 如果要创建新 DSN,请确定是只对自己可见(用户 DSN)、对包含 Windows 服务的计算机的所有用户可见(系统 DSN)还是将其保存在文件中(文件 DSN) 。If you want to create a new DSN, decide whether you want it to be visible only to you (User DSN), visible to all users of the computer including Windows services (System DSN), or saved in a file (File DSN). 此示例创建一个新的系统 DSN。This example creates a new System DSN.

  5. 在“系统 DSN”选项卡上,单击“添加” 。On the System DSN tab, click Add.

    添加新的 ODBC 系统 DSN

  6. 在“新建数据源”对话框中,选择数据源的驱动程序,然后单击“完成” 。In the Create a New Data Source dialog box, select the driver for your data source, then click Finish.

    为新系统 DSN 选取驱动程序

  7. 驱动程序现在显示一个或多个驱动程序专用的屏幕,可在其中输入连接数据源所需的信息。The driver now displays one or more driver-specific screens where you enter the info needed to connect to your data source. (例如,对于 SQL Server 驱动程序,自定义设置有 4 页。)完成后,新的系统 DSN 将出现在列表中。(For the SQL Server driver, for example, there are four pages of custom settings.) After you finish, the new system DSN appears in the list.

    列表中的新系统 DSN

  8. 返回向导,在“选择数据源”页或“选择目标”页上的“DSN”字段中输入 DSN 名称 。Go back to the wizard and enter the DSN name in the Dsn field on the Choose a Data Source or Choose a Destination page. 继续执行向导的下一步。Continue to the next step of the wizard.

选项 2 - 提供一个连接字符串

Option 2 - Provide a connection string 如果要在连接信息中提供连接字符串,可借助本主题的其余内容获取所需的连接字符串。If you want to provide your connection information with a connection string, the rest of this topic helps you get the connection string you need.

本示例将使用以下连接字符串,该字符串与 Microsoft SQL Server 连接。This example is going to use the following connection string, which connects to Microsoft SQL Server. 使用的数据库示例是 WideWorldImporters,我们将连接到本地计算机上的 SQL Server 。The database example that is used is WideWorldImporters and we're connecting to the SQL Server on the local machine.

```
Driver={ODBC Driver 13 for SQL Server};server=localhost;database=WideWorldImporters;trusted_connection=Yes;
```

在“选择数据源”页或“选择目标”页上的“ConnectionString”字段中输入连接字符串 。Enter the connection string in the ConnectionString field on the Choose a Data Source or Choose a Destination page. 输入连接字符串后,向导会分析该字符串,并在列表中显示各个属性及其值。After you enter the connection string, the wizard parses the string and displays the individual properties and their values in the list.

下面是输入连接字符串后出现的屏幕。Here's the screen that you see after entering the connection string.

之后使用 ODBC 连接到 SQL

备注

不管是配置源还是目标,ODBC 驱动程序的连接选项都相同。The connection options for an ODBC driver are the same whether you're configuring your source or your destination. 也就是说,在向导的“选择数据源”页和“选择目标”页上看到的选项是相同的 。That is, the options you see are the same on both the Choose a Data Source and the Choose a Destination pages of the wizard.

联机获取连接字符串Get the connection string online

要联机为 ODBC 驱动程序查找连接字符串,请参阅连接字符串参考To find connection strings for your ODBC driver online, see The Connection Strings Reference. 该第三方站点包含示例连接字符串、关于数据提供程序的详细信息以及它们需要的连接信息。This third-party site contains sample connection strings and more info about data providers and the connection info they require.

使用应用获取连接字符串Get the connection string with an app

若要在自己的计算机上生成并测试用于 ODBC 驱动程序的连接字符串,可以使用“控制面板”中的“ODBC 数据源管理器”小程序 。To build and test the connection string for your ODBC driver on your own computer, you can use the ODBC Data Source Administrator applet in the Control Panel. 为连接创建一个文件 DSN,然后将设置从文件 DSN 复制出来,组合成连接字符串。Create a File DSN for your connection, then copy settings out of the File DSN to assemble the connection string. 这需要执行多个步骤,但有助于确保连接字符串有效。This requires several steps, but helps to make sure you have a valid connection string.

  1. 在开始菜单或控制面板中搜索或浏览到“ODBC 数据源 (64 位)”小程序 。Search for or browse to the ODBC Data Sources (64-bit) applet in the Start Menu or Control Panel. 如果只有 32 位驱动程序,或必须使用 32 位驱动程序,请改为搜索或浏览到“ODBC 数据源(32 位)” 。If you only have a 32-bit driver, or have to use a 32-bit driver, search for or browse to ODBC Data Sources (32-bit) instead.

  2. 启动小程序。Launch the applet. 此时会打开“ODBC 数据源管理器”窗口 。The ODBC Data Source Administrator window opens.

  3. 现在,转到小程序的“文件 DSN”选项卡 。Now go to the File DSN tab of the applet. 单击“添加” 。Click Add.

    对于本示例,创建文件 DSN 而不是用户 DSN 或系统 DSN,因为文件 DSN 会以连接字符串所需的特定格式来保存名称-值对。For this example, create a File DSN rather than a User DSN or System DSN, because the File DSN saves the name-value pairs in the specific format required for the connection string.

    添加新的 ODBC 文件 DSN

  4. 在“新建数据源”对话框中,选择列表中的驱动程序,然后单击“下一步” 。In the Create New Data Source dialog box, select your driver in the list, and then click Next. 此示例会创建一个 DSN,其中包含连接 Microsoft SQL Server 时所需的连接字符串参数。This example is going to create a DSN that contains the connection string arguments we need to connect to Microsoft SQL Server.

    新建 ODBC 数据源

  5. 选择一个位置并为新文件 DSN 输入文件名,然后单击“下一步” 。Select a location and enter a filename for the new File DSN, and then click Next. 请记住文件的保存位置,以便在后续步骤中可查找并打开文件。Remember where you save the file so you can find it and open it in a subsequent step.

    保存新文件 DSN

  6. 检查所选内容的摘要,然后单击“完成” 。Review the summary of your selections, and then click Finish.

  7. 单击“完成”后,所选驱动程序将显示一个或多个专用屏幕,以收集连接时所需信息 。After you click Finish, the driver that you selected displays one or more proprietary screens to gather the info it needs to connect. 通常,该信息包含基于服务器的数据源的服务器、登录信息和数据库,以及基于文件的数据源的文件、格式和版本。Typically this info includes server, login info, and database for server-based data sources, and file, format, and version for file-based data sources.

  8. 配置数据源并单击“完成”后,通常会看到所选内容的摘要,并有机会对其进行测试 。After you configure your data source and click Finish, you typically see a summary of your selections and have an opportunity to test them.

    测试新文件 DSN

  9. 测试数据源并关闭对话框后,在文件系统中文件 DSN 的保存位置查找它。After you test your data source and close the dialog boxes, find the File DSN where you saved it in the file system. 如果未更改文件扩展名,默认扩展名为 .DSN。If you didn't change the file extension, the default extension is .DSN.

  10. 使用记事本或其他文本编辑器打开保存的文件。Open the saved file with Notepad or another text editor. 以下是 SQL Server 示例的内容。Here are the contents of our SQL Server example.

    [ODBC]  
    DRIVER=ODBC Driver 13 for SQL Server  
    TrustServerCertificate=No  
    DATABASE=WideWorldImporters    
    WSID=<local computer name>  
    APP=Microsoft® Windows® Operating System  
    Trusted_Connection=Yes  
    SERVER=localhost   
    
  11. 将必需的值复制并粘贴到连接字符串中,其中使用分号分隔名称-值对。Copy and paste the necessary values into a connection string in which the name-value pairs are separated by semi-colons.

    将示例文件 DSN 中必需的值进行组合后,会得到下面的连接字符串。After you assemble the necessary values from the sample file DSN, you have the following connection string.

    ```
    DRIVER=ODBC Driver 13 for SQL Server;SERVER=localhost;DATABASE=WideWorldImporters;Trusted_Connection=Yes
    ```
    

    通常无需用到 ODBC 数据源管理器创建的 DSN 中的全部设置,即可创建正常运行的连接字符串。You don't typically need all the settings in a DSN created by the ODBC Data Source Administrator to create a connection string that works.

    • 始终需要指定 ODBC 驱动程序。You always have to specify the ODBC driver.
    • 对于 SQL Server 这类基于服务器的数据源,通常需要服务器、数据库和登录信息。For a server-based data source like SQL Server, you typically need Server, Database, and login information. 在示例 DSN 中,不需要 TrustServerCertificate、WSID 或 APP。In the sample DSN, you don't need TrustServerCertificate, WSID, or APP.
    • 对于基于文件的数据源,至少需要文件名和位置。For a file-based data source, you need at least file name and location.
  12. 在向导“选择数据源”页或“选择目标”页上的“ConnectionString”字段中粘贴此连接字符串 。Paste this connection string into the ConnectionString field on the Choose a Data Source or Choose a Destination page of the wizard. 向导会分析字符串,可继续操作!The wizard parses the string and you're ready to continue!

    之后使用 ODBC 连接到 SQL

另请参阅See also

选择数据源 Choose a Data Source
选择目标 Choose a Destination