TechNet
Export (0) Print
Expand All

Choose a Destination (SQL Server Import and Export Wizard)

 

Updated: August 22, 2016

Applies To: SQL Server 2016

After you provide info about the source of your data and about how to connect to it, the SQL Server Import and Export Wizard shows Choose a Destination. On this page, you provide info about the destination for your data and about how to connect to it.

For info about the data destinations that you can use, see What data sources and destinations can I use?

The following screen shot shows the part of the Choose a Destination page of the wizard that doesn't change.

Choose destination

Destination
Specify the destination by selecting a data provider that can import data into the destination. In most cases, the data provider that you need is obvious from its name, because the name of the provider contains the name of the destination - for example, SQL Server, Oracle, flat files, Excel, and Access.

The list of available data providers in the Destination list depends on the providers installed on your computer. It also depends on whether you're running the 64-bit or the 32-bit wizard.

There may be more than one provider available for your destination. Typically you can select any provider that works with your destination. For example, to connect to Microsoft SQL Server, you can use SQL Server Native Client, the Microsoft OLE DB Provider for SQL Server, or the .NET Framework Data Provider for SQL Server.

In some cases, you have to start by picking a generic data provider. For example, if you have an ODBC driver for your destination, select the .Net Framework Data Provider for ODBC.

For some destinations, you may have to download the data provider from Microsoft or from a third party. For info about the data destinations that you can use, see What data sources and destinations can I use?

After you choose a destination, the rest of the Choose a Destination page property has a variable number of options which depend on the data provider that you choose.

The following sections list the important options for some frequently used destinations. Not all the destinations that may be available in the Destination drop-down are listed here. For additional options and for other providers, see the provider-specific documentation.

System_CAPS_ICON_tip.jpg Tip

If your destination requires a connection string, you can find examples on this third-party site - The Connection Strings Reference.

If you want to create a new SQL Server database as the destination, select SQL Server Native Client or the Microsoft OLE DB Provider for SQL Server. If you select the .Net Framework Data Provider for SQL Server, the option to create a new database is not available.

Connect to SQL Server with SQL Server Native Client or the Microsoft OLE DB Provider for SQL Server

SQL Server Native Client and the Microsoft OLE DB Provider for SQL Server expose the same set of options. The following screen shot shows the options for SQL Server Native Client as an example.

SQL native destination

Server name
Type the name of the destination server , or choose a server from the list.

System_CAPS_ICON_note.jpg Note

If you're on a network with multiple servers, it may be easier to type in the server name. If you click the drop-down list, it may take a lot of time to query the network for all available servers, and your server name may not be listed in the results.

Use Windows Authentication
Specify whether the wizard should use Microsoft Windows Authentication to log in to the database. Windows Authentication is recommended.

Use SQL Server Authentication
Specify whether the wizard should use SQL Server Authentication to log in to the database. If you use SQL Server Authentication, you must provide a user name and password.

User name
Provide a user name for the database connection when you are using SQL Server Authentication.

Password
Provide the password for the database connection when you are using SQL Server Authentication.

Database
Select from the list of databases on the specified instance of SQL Server.

Refresh
Restore the list of available databases by clicking Refresh.

Connect to SQL Server with the .NET Framework Data Provider for SQL Server

This page presents a grouped list of options for the .NET Framework Data Provider for SQL Server. The important options are listed here. The additional options that are listed when you select this provider are not required to connect successfully to the SQL Server destination database.

For more info, see .NET Framework Data Provider for SQL Server connection strings.

SQL Server destination net

Destination
Type the name of the destination server, or choose a server from the list.

System_CAPS_ICON_note.jpg Note

If you're on a network with multiple servers, it may be easier to type in the server name. If you click the drop-down list, it may take a lot of time to query the network for all available servers, and your server name may not be listed in the results.

Initial Catalog
Type the name of the destination database, or choose a database from the list.

Integrated Security
Specify True to connect by using Windows integrated authentication, which is recommended, or False to connect by using SQL Server Authentication. If you specify False, you must enter a user ID and password. The default value is False.

User ID
Provide a user name for the database connection when you are using SQL Server Authentication.

Password
Provide the password for the database connection when you are using SQL Server Authentication.

Connect to Oracle by using the .Net Framework Data Provider for Oracle or the Microsoft OLE DB Provider for Oracle. The .Net Framework Data Provider for Oracle is easier to configure and is shown in the following screen shot.

For more info, see .NET Framework Data Provider for Oracle connection strings or Microsoft OLE DB Provider for Oracle connection strings.

Oracle destination

To save data to any destination that provides an ODBC driver, select the .Net Framework Data Provider for ODBC.

To provide a connection string for an ODBC destination, see the documentation for the ODBC driver that you want to use, or look for examples here - The Connection Strings Reference.

The following screen shot shows an ODBC connection to SQL Server as an example. The connection string used in the example is:

Driver={SQL Server};Server=(local);Database=TestData;Trusted_Connection=Yes;

After you enter the connection string in the ConnectionString field, the wizard parses the string and displays the individual properties and their values in the Misc section of the list.

ODBC connection SQL destination

Flat file destination

File name
Specify the path and file name for the file in which to store the data. Or, click Browse to locate a file.

Browse
Locate a file by using the Open dialog box.

Locale
Specify the locale ID (LCID) that defines character sort orders and date and time formatting.

Unicode
Indicate whether to use Unicode. If you use Unicode, you do not have to specify a code page.

Code page
Specify the code page for the language you want to use.

Format
Indicate whether to use delimited, fixed width, or ragged right formatting.

ValueDescription
DelimitedColumns are separated by a delimiter.
Fixed widthColumns have a fixed width.
Ragged rightRagged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter.

Text qualifier
Type the text qualifier to use. For example, you can specify that each text column be surrounded with quotation marks.

Column names in first data row
Indicate whether you want to display column names in the first data row.

The following screen shot shows a sample connection to a Microsoft Excel workbook.

Excel destination

Excel file path
Specify the path and file name for the spreadsheet to which to export the data. For example, C:\MyData.xlsx or \\Sales\Database\Northwind.xlsx. Or, click Browse.

Browse
Locate the spreadsheet by using the Open dialog box.

Excel version
Select the version of Excel that is used by the destination workbook.

System_CAPS_ICON_note.jpg Note

When Microsoft Excel is your destination, the wizard uses the Integration Services Excel Destination component. For information about usage considerations and known issues, see Excel Destination.

The following screen shot shows a sample connection to a Microsoft Access database.

Access destination

File name
Specify the path and file name for the database file to which to export the data. For example, C:\MyData.mdb, \\Sales\Database\Northwind.mdb. Or, click Browse.

System_CAPS_ICON_note.jpg Note

The wizard can only connect to an Access database in the .MDB file format.

Browse
Locate the database file by using the Open dialog box.

User name
When a workgroup information file is associated with the database, provide a valid user name for the database connection .

Password
When a workgroup information file is associated with the database, provide the user's password for the database connection.

If the database is protected with a single password for all users, provide this value in the Data Link Properties dialog box. To open the Data Link Properties dialog box, click Advanced.

Advanced
Specify advanced options, such as the database password or a non-default workgroup information file, by using the Data Link Properties dialog box.

You may have to download the OLE DB providers and drivers for Microsoft Office files if they're not already installed. Later versions of the provider can open files created by earlier versions of Excel.

If the computer has a 32-bit version of Office, then you have to install the 32-bit version of the drivers, and you also have to ensure that you run the wizard or the Integration Services package that it creates in 32-bit mode.

Microsoft Office versionDownload
20072007 Office System Driver: Data Connectivity Components
2010Microsoft Access 2010 Runtime
2013Microsoft Access 2013 Runtime
2016Microsoft Access 2016 Runtime

To use the Azure Blob Destination, you have to install the Azure Feature Pack for SSIS. For more info, see Azure Feature Pack for Integration Services (SSIS).

System_CAPS_ICON_note.jpg Note

To ensure that the Azure Storage Connection Manager and the components that use it, including the Blob Destination, can connect to both general-purpose storage accounts and to blob storage accounts, make sure you download the latest version of the Azure Feature Pack here. For more info about these two types of storage accounts, see Introduction to Microsoft Azure Storage.

Azure blob destination

Use Azure account
Specify whether to use an online account.

Storage account name
Specify the name of the Azure storage account.

Account key
Provide the key for the Azure storage account.

Use HTTPS
Specify whether to use HTTP or HTTPS to connect to the storage account.

Use local developer account
Specify whether to use the storage emulator on the local computer.

Blob container name
Select from the list of storage containers available in the specified storage account.

Blob file format
Select Text or Avro file format.

Column delimiter character
If you selected Text format, specify the column delimiter character.

Use first row as column names
Specify whether the first row of data contains column names.

After you provide info about the destination for your data and about how to connect to it, the next page is Specify Table Copy or Query. On this page, you specify whether you want to copy an entire table or only certain rows. For more info, see Specify Table Copy or Query.

Community Additions

ADD
Show:
© 2016 Microsoft