Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Connect to an ODBC Data Source (SQL Server Import and Export Wizard)
In this articleApplies to: SQL Server (all supported versions) SSIS Integration Runtime in Azure Data Factory 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. 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. 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
Tip 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. This also tells you whether you have to run the 64-bit or 32-bit SQL Server Import and Export Wizard. 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) and SQL Server Management Studio (SSMS) are 32-bit applications and only install 32-bit files, including the 32-bit version of the wizard. Step 1 - Select the data sourceThe ODBC drivers installed on your computer aren't listed in the drop-down list of data sources. 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. This provider acts as a wrapper around the ODBC driver. Here's the generic screen that you see immediately after selecting the .NET Framework Data Provider for ODBC. Step 2 - Provide the connection infoThe next step is to provide the connection info for your ODBC driver and your data source. You have two options.
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. If you provide a DSN, this information isn't visible. Option 1 - Provide a DSNIf 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.
Option 2 - Provide a connection stringIf you want to provide your connection information with a connection string, the rest of this topic helps you get the connection string you need. This example is going to use the following connection string, which connects to Microsoft SQL Server. The database example that is used is WideWorldImporters and we're connecting to the SQL Server on the local machine.
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. Note 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 onlineTo 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 appTo 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. 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.
See alsoChoose a Data
Source How do I connect ODBC with Excel?Case Study. Select Start, Settings, Control Panel, Administrative Tools, Data Sources (ODBC). ... . Navigate to the System DSN tab and click Add to open the Create New Data Source dialog box.. Select Microsoft Excel Driver as the driver for which you want to set up the data source. ... . Specify a name for the data source.. How does ODBC connect to SQL Server?How To - Use ODBC To Test and Validate Connectivity to SQL Database Server. Click Start > Settings > Control Panel.. Double-click Administrative Tools.. Double-click ODBC Data Sources (32-bit). ... . Click the System DSN tab.. Click Add.. In the Create New Data Source window, choose SQL Server and click Finish.. Is Excel ODBC compliant?Strict compliance with Microsoft ODBC 3.52 Specification
Our ODBC drivers provide full support for the standard Open Database Connectivity API functions and data types. ODBC interface allows Excel to connect to ODBC databases using the SQL language.
What is ODBC Excel driver?The Excel ODBC Driver is a powerful tool that allows you to connect with live Excel file data, directly from any applications that support ODBC connectivity. Access Excel Spreadsheet data like you would a database - read, write, and update data through a standard ODBC Driver interface.
|