You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Copy file name to clipboardExpand all lines: docs/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard.md
+37-37Lines changed: 37 additions & 37 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -4,7 +4,7 @@ description: How to configure an ODBC DSN or create an ODBC connection string to
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.
16
+
This article demonstrates 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.
18
17
19
18
You may have to download the ODBC driver you need from Microsoft or from a third party.
20
19
21
20
You may also have to look up the required connection info that you have to provide. This third-party site - [The Connection Strings Reference](https://www.connectionstrings.com/) - contains sample connection strings and more info about data providers and the connection info they require.
22
21
23
22
## Make sure the driver you want is installed
24
-
1. Search for or browse to the **ODBC Data Sources (64-bit)** applet in the Start Menu or Control Panel. 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.
25
-
2. Launch the applet. The **ODBC Data Source Administrator** window opens.
26
-
3. 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.)
23
+
24
+
1. Search for or browse to the **ODBC Data Sources (64-bit)** applet in the Start Menu or Control Panel. 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.
25
+
2. Launch the applet. The **ODBC Data Source Administrator** window opens.
26
+
3. 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.)
27
27
28
28
Here's an example of the list of installed 64-bit drivers.
29
29
@@ -33,8 +33,9 @@ You may also have to look up the required connection info that you have to provi
33
33
> 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.
34
34
>
35
35
> 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.
36
-
36
+
37
37
## Step 1 - Select the data source
38
+
38
39
The 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.
39
40
40
41
Here's the generic screen that you see immediately after selecting the .NET Framework Data Provider for ODBC.
@@ -43,13 +44,13 @@ Here's the generic screen that you see immediately after selecting the .NET Fram
43
44
44
45
## Step 2 - Provide the connection info
45
46
The next step is to provide the connection info for your ODBC driver and your data source. You have two options.
46
-
1.Provide a **DSN** (data source name) that already exists or that you create with the **ODBC Data Source Administrator** applet. A DSN is the saved collection of settings required to connect to an ODBC data source.
47
+
1. Provide a **DSN** (data source name) that already exists or that you create with the **ODBC Data Source Administrator** applet. A DSN is the saved collection of settings required to connect to an ODBC data source.
47
48
48
49
If you already know the DSN name, or know how to create a new DSN now, you can skip the rest of this page. 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.
49
50
50
51
[Provide a DSN](#odbc_dsn)
51
52
52
-
2.Provide a **connection string**, which you can look up online, or create and test on your computer with the **ODBC Data Source Administrator** applet.
53
+
2. Provide a **connection string**, which you can look up online, or create and test on your computer with the **ODBC Data Source Administrator** applet.
53
54
54
55
If you already have the connection string or know how to create it, you can skip the rest of this page. 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.
55
56
@@ -59,26 +60,26 @@ If you provide a connection string, the **Choose a Data Source** or **Choose a D
59
60
60
61
## <aname="odbc_dsn"></a> Option 1 - Provide a DSN
61
62
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.
62
-
1.Search for or browse to the **ODBC Data Sources (64-bit)** applet in the Start Menu or Control Panel. 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.
63
-
2.Launch the applet. The **ODBC Data Source Administrator** window opens. Here's what the applet looks like.
63
+
1. Search for or browse to the **ODBC Data Sources (64-bit)** applet in the Start Menu or Control Panel. 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.
64
+
2. Launch the applet. The **ODBC Data Source Administrator** window opens. Here's what the applet looks like.
64
65
65
66
66
-
67
-
3.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.
68
-
4.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). This example creates a new System DSN.
67
+
dd
68
+
3. 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.
69
+
4. 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). This example creates a new System DSN.
69
70
5. On the **System DSN** tab, click **Add**.
70
71
71
72
72
-
73
-
6.In the **Create a New Data Source** dialog box, select the driver for your data source, then click **Finish**.
73
+
74
+
6. In the **Create a New Data Source** dialog box, select the driver for your data source, then click **Finish**.
74
75
75
76
76
-
77
+
77
78
7. The driver now displays one or more driver-specific screens where you enter the info needed to connect to your data source. (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.
78
79
79
80
80
-
81
-
8.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.
81
+
82
+
8. 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.
82
83
83
84
## <aname="odbc_connstring"></a> Option 2 - Provide a connection string
84
85
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.
@@ -104,25 +105,25 @@ To find connection strings for your ODBC driver online, see [The Connection Stri
104
105
## Get the connection string with an app
105
106
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. 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.
106
107
107
-
1.Search for or browse to the **ODBC Data Sources (64-bit)** applet in the Start Menu or Control Panel. 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.
108
-
2.Launch the applet. The **ODBC Data Source Administrator** window opens.
109
-
3.Now go to the **File DSN** tab of the applet. Click **Add**.
108
+
1. Search for or browse to the **ODBC Data Sources (64-bit)** applet in the Start Menu or Control Panel. 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.
109
+
2. Launch the applet. The **ODBC Data Source Administrator** window opens.
110
+
3. Now go to the **File DSN** tab of the applet. Click **Add**.
110
111
111
112
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.
112
113
113
114
114
115
115
-
4.In the **Create New Data Source** dialog box, select your driver in the list, and then click **Next**. This example is going to create a DSN that contains the connection string arguments we need to connect to Microsoft SQL Server.
116
+
4. In the **Create New Data Source** dialog box, select your driver in the list, and then click **Next**. This example is going to create a DSN that contains the connection string arguments we need to connect to Microsoft SQL Server.
116
117
117
118
118
-
119
-
5.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.
119
+
120
+
5. 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.
120
121
121
122
122
123
123
-
6.Review the summary of your selections, and then click **Finish**.
124
+
6. Review the summary of your selections, and then click **Finish**.
124
125
125
-
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.
126
+
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.
126
127
127
128
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.
128
129
@@ -142,26 +143,25 @@ To build and test the connection string for your ODBC driver on your own compute
142
143
Trusted_Connection=Yes
143
144
SERVER=localhost
144
145
```
145
-
146
+
146
147
11. Copy and paste the necessary values into a connection string in which the name-value pairs are separated by semi-colons.
147
148
148
149
After you assemble the necessary values from the sample file DSN, you have the following connection string.
149
-
150
+
150
151
```console
151
152
DRIVER=ODBC Driver 13 for SQL Server;SERVER=localhost;DATABASE=WideWorldImporters;Trusted_Connection=Yes
152
153
```
153
154
154
-
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.
155
-
- You always have to specify the ODBC driver.
156
-
- For a server-based data source like SQL Server, you typically need Server, Database, and login information. In the sample DSN, you don't need TrustServerCertificate, WSID, or APP.
157
-
- For a file-based data source, you need at least file name and location.
158
-
155
+
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.
156
+
- You always have to specify the ODBC driver.
157
+
- For a server-based data source like SQL Server, you typically need Server, Database, and login information. In the sample DSN, you don't need TrustServerCertificate, WSID, or APP.
158
+
- For a file-based data source, you need at least file name and location.
159
+
159
160
12. 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!
160
161
161
162
162
163
163
164
## See also
164
-
[Choose a Data Source](../../integration-services/import-export-data/choose-a-data-source-sql-server-import-and-export-wizard.md)
165
-
[Choose a Destination](../../integration-services/import-export-data/choose-a-destination-sql-server-import-and-export-wizard.md)
166
-
167
165
166
+
[Choose a Data Source](../../integration-services/import-export-data/choose-a-data-source-sql-server-import-and-export-wizard.md)
167
+
[Choose a Destination](../../integration-services/import-export-data/choose-a-destination-sql-server-import-and-export-wizard.md)
0 commit comments