Share via


Choose a Data Source (SQL Server Import and Export Wizard)

Use the Choose a Data Source page to specify the source of the data that you want to copy.

To learn more about this wizard, see Creating Packages Using the SQL Server Import and Export Wizard. To learn about the options for starting the wizard, and about the permissions required to run the wizard successfully, see How to: Start the SQL Server Import and Export Wizard.

The purpose of the SQL Server Import and Export Wizard is to copy data from a source to a destination. The wizard can also create a destination database and destination tables for you. However, if you have to copy multiple databases or tables, or other kinds of database objects, you should use the Copy Database Wizard instead. For more information, see Using the Copy Database Wizard.

Options

  • Data Source
    Choose the data provider that matches the data storage format of the source. There may be more than one provider available for your data source. For example, with SQL Server you can use SQL Native Client, the .Net Framework Data Provider for Sql Server, or the Microsoft OLE DB Provider for SQL Server.

    Note

    To connect to a data source that uses Microsoft Office Excel 2003 or earlier, for Data Source, select Microsoft Excel. To connect to a data source that uses Microsoft Office Excel 2007, for Data Source, select Microsoft Office 12.0 Access Database Engine OLE DB Provider, click Properties, and then, on the All tab of the Data Link Properties dialog box, for Extended Properties, enter Excel 12.0. To connect to a database that uses Microsoft Office Access 2003 or earlier, for Data Source, select Microsoft Access. To connect to a database that uses Microsoft Office Access 2007, for Data Source, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. To connect to data sources that use either Excel 2007 or Access 2007, you cannot select Microsoft Jet 4.0 OLE DB Provider.

    Note

    When you connect to an ODBC source by selecting .Net Framework Data Provider for Odbc, the provider is unable to enumerate the tables and views available at the source. Therefore the Copy data from one or more tables or view option is dimmed on the Specify Table Copy or Query page, and you must write a query to specify the data to transfer.

    Note

    In SQL Server 2005, the SQL Server Import and Export Wizard does not support importing from or exporting to dBASE or other DBF files. To import DBF files, first use Microsoft Access or Microsoft Excel to import the data from DBF files into an Access database or Excel spreadsheets. Then, use the SQL Server Import and Export Wizard to import the Access database or Excel spreadsheets that contain the data from the DBF files.

    The Data Source property has a variable number of options, which depend on the providers installed on the computer. The following tables list the options for some frequently used destinations. For other providers, see the provider-specific documentation.

Dynamic Options

The following sections show the options available for several data sources. Not all the data sources that are available in the Data Source drop-down are listed here.

Data Source = SQL Native Client and Microsoft OLE DB Provider for SQL Server

  • Server name
    Type the name of the server that contains the data, or choose a server from the list.
  • Use Windows Authentication
    Specify whether the package should use Microsoft Windows Authentication to log in to the database. Windows Authentication is recommended for better security.
  • Use SQL Server Authentication
    Specify whether the package 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 as follows.
  • User name
    Specify 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.

Data Source = .NET Framework Data Provider for SQL Server

This page presents an alphabetical list of options for the .NET Framework Data Provider for SQL Server. The most important options are listed in the following table.

  • Data Source
    Type the name of the server that contains the data, or choose a server from the list.
  • Initial Catalog
    Type the name of the source database.
  • 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
    Specify 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.

The additional options that are listed when you select this provider are not required to connect successfully to the SQL Server source database. For a description of these additional options, see the documentation for the .NET Framework Data Provider for SQL Server in the .NET Framework Software Development Kit.

Data Source = Microsoft Excel

Note

Select Microsoft Excel only if you want to connect to a data source that uses Excel 2003 or earlier. To connect to a data source that uses Excel 2007, select Microsoft Office 12.0 Access Database Engine OLE DB Provider, click Properties, and then on the All tab of the Data Link Properties dialog box, enter Excel 12.0 as the value for Extended Properties.

  • Excel file path
    Specify the path and file name for the spreadsheet from which to import the data. For example, C:\MyData.xls, \\Sales\Database\Northwind.xls. Or, click Browse.
  • Browse
    Locate the spreadsheet by using the Open dialog box.
  • Excel version
    Select the version of Excel that the source data is stored in.

Note

When you import data from a Microsoft Excel source, the wizard uses the Integration Services Excel Source component. For information about usage considerations and known issues, see Excel Source.

Data Source = Microsoft Access

Note

Select Microsoft Access only if you want to connect to a database that uses Access 2003 or earlier. To connect to a database that uses Access 2007, select Microsoft Office 12.0 Access Database Engine OLE DB Provider instead.

  • File name
    Specify the path and file name for the database file from which to import the data. For example, C:\MyData.mdb, \\Sales\Database\Northwind.mdb. Or, click Browse.
  • Browse
    Locate the database file by using the Open dialog box.
  • User name
    Specify a valid user name for the database connection when a workgroup information file is associated with the database.
  • Password
    Provide the user's password for the database connection when a workgroup information file is associated with the database. However, if the database is protected with a single password for all users, you must provide this value in the Data Link Properties dialog box, which is accessed by clicking Advanced.
  • Advanced
    You may want to specify advanced options, such as the database password or a non-default workgroup information file, by using the Data Link Properties dialog box. For more information about OLE DB provider properties, search in the Data Access section of the MSDN library at https://msdn.microsoft.com/library.

Data Source = Flat File Source

See the following topics for information about the options for a flat file data source.

Flat File Connection Manager Editor (General Page)

Flat File Connection Manager Editor (Columns Page)

Flat File Connection Manager Editor (Advanced Page)

Flat File Connection Manager Editor (Preview Page)

Change History

Release History

15 September 2007

New content:
  • Added a note about how to import or export dBASE or other DBF files.

12 December 2006

New content:
  • Added a note about the use of either Excel 2007 or Access 2007.

17 July 2006

Changed content:
  • Added detail about individual providers.
  • Added link to information about the Excel source.
  • Consolidated options for SQL Native Client and Microsoft OLE DB Provider for SQL Server into single table.

14 April 2006

Changed content:
  • Added note about enumerating objects from an ODBC source.