Import information from Excel or export data to Excel with SQL Server Integration Services (SSIS)

  • Article
  • 12 minutes to read

Applies to: yesSQL Server (all supported versions) yes SSIS Integration Runtime in Azure Data Factory

This article describes the connection information that you accept to provide, and the settings that you have to configure, to import data from Excel or export data to Excel with SQL Server Integration Services (SSIS).

The following sections contain the information y'all need to use Excel successfully with SSIS, and to understand and troubleshoot common bug:

  1. The tools you tin use.

  2. The files you demand.

  3. The connexion information that you have to provide, and the settings that you have to configure, when you load data from or to Excel with SSIS.

    • Specify Excel as your data source.
    • Provide the Excel file name and path.
    • Select the Excel version.
    • Specify whether the offset row contains column names.
    • Provide the worksheet or range that contains the data.

  4. Known issues and limitations.

    • Problems with data types.
    • Issues with importing.
    • Issues with exporting.

Y'all can import data from Excel or export data to Excel with SSIS by using one of the post-obit tools:

  • SQL Server Integration Services (SSIS). Create an SSIS parcel that uses the Excel Source or the Excel Destination with the Excel Connection Manager. (This article does not describe how to create SSIS packages.)

  • The SQL Server Import and Consign Wizard, which is built on SSIS. For more than info, come across Import and Export Data with the SQL Server Import and Export Sorcerer and Connect to an Excel Data Source (SQL Server Import and Export Wizard).

Get the files you need to connect to Excel

Before you tin import data from Excel or export data to Excel with SSIS, yous may have to download the connectivity components for Excel if they're not already installed. The connectivity components for Excel are not installed by default.

Use the table within Unable to use the Admission ODBC, OLEDB or DAO interfaces outside Office Click-to-Run to empathise if additional components are necessary for your environment.

Note: The Office System Drivers are just supported under sure scenarios, please refer to Considerations for server-side Automation of Role for specific guidance.

Specify Excel as your data source

The first step is to indicate that you want to connect to Excel.

In SSIS

In SSIS, create an Excel Connectedness Manager to connect to the Excel source or destination file. There are several ways to create the connectedness managing director:

  • In the Connection Managers area, right-click and select New connection. In the Add SSIS Connection Manager dialog box, select EXCEL so Add together.

  • On the SSIS carte du jour, select New connection. In the Add together SSIS Connection Director dialog box, select EXCEL and then Add.

  • Create the connection managing director at the same time that you configure the Excel Source or the Excel Destination on the Connection manager page of the Excel Source Editor or of the Excel Destination Editor.

In the SQL Server Import and Export Magician

In the Import and Export Wizard, on the Choose a Data Source or Choose a Destination page, select Microsoft Excel in the Data source list.

If you don't see Excel in the list of information sources, make sure y'all're running the 32-bit sorcerer. The Excel connectivity components are typically 32-bit files and aren't visible in the 64-scrap magician.

Excel file and file path

The kickoff piece of info to provide is the path and file name for the Excel file. Yous provide this info in the Excel Connexion Managing director Editor in an SSIS package, or on the Choose a Data Source or Cull a Destination page of the Import and Consign Magician.

Enter the path and file proper noun in the following format:

  • For a file on the local computer, C:\TestData.xlsx.

  • For a file on a network share, \\Sales\Information\TestData.xlsx.

Or, click Scan to locate the spreadsheet by using the Open dialog box.

Important

Y'all can't connect to a password-protected Excel file.

Excel version

The 2d piece of info to provide is the version of the Excel file. Y'all provide this info in the Excel Connectedness Managing director Editor in an SSIS package, or on the Cull a Data Source or Cull a Destination page of the Import and Export Wizard.

Select the version of Microsoft Excel that was used to create the file, or another uniform version. For case, if you had problem installing the 2016 connectivity components, you lot can install the 2010 components and select Microsoft Excel 2007-2010 in this listing.

You lot may not be able to select newer Excel versions in the list if you only take older versions of the connectivity components installed. The Excel version list includes all the versions of Excel supported by SSIS. The presence of items in this list does not indicate that the required connectivity components are installed. For example, Microsoft Excel 2016 appears in the list even if you have not installed the 2016 connectivity components.

Showtime row has column names

If you're importing information from Excel, the next step is to indicate whether the commencement row of the information contains cavalcade names. You provide this info in the Excel Connection Manager Editor in an SSIS package, or on the Choose a Data Source folio of the Import and Consign Magician.

  • If you lot disable this option considering the source data doesn't contain column names, the wizard uses F1, F2, and so forth, as column headings.
  • If the information contains cavalcade names, but you lot disable this pick, the magician imports the cavalcade names as the kickoff row of data.
  • If the data does not contain column names, but you lot enable this option, the wizard uses the beginning row of source data as the column names. In this case, the first row of source data is no longer included in the data itself.

If you're exporting data from Excel, and y'all enable this option, the start row of exported data includes the column names.

Worksheets and ranges

There are three types of Excel objects that you can utilise as the source or destination for your data: a worksheet, a named range, or an unnamed range of cells that yous specify with its accost.

  • Worksheet. To specify a worksheet, suspend the $ character to the end of the sheet name and add delimiters effectually the string - for example, [Sheet1$]. Or, look for a name that ends with the $ character in the list of existing tables and views.

  • Named range. To specify a named range, provide the range name - for case, MyDataRange. Or, wait for a proper name that does not end with the $ character in the list of existing tables and views.

  • Unnamed range. To specify a range of cells that you haven't named, append the $ character to the end of the sheet proper name, add together the range specification, and add delimiters around the cord - for example, [Sheet1$A1:B4].

To select or specify the blazon of Excel object that you want to apply every bit the source or destination for your data, do one of the following things:

In SSIS

In SSIS, on the Connexion manager folio of the Excel Source Editor or of the Excel Destination Editor, exercise ane of the following things:

  • To utilize a worksheet or a named range, select Table or view every bit the Data access fashion. Then, in the Proper noun of the Excel canvas listing, select the worksheet or named range.

  • To use an unnamed range that you specify with its accost, select SQL command as the Data admission way. Then, in the SQL control text field, enter a query like the following example:

                      SELECT * FROM [Sheet1$A1:B5]

In the SQL Server Import and Export Wizard

In the Import and Export Wizard, do one of the following things:

  • When y'all're importing from Excel, do one of the post-obit things:

    • To employ a worksheet or a named range, on the Specify table copy or query page, select Copy information from one or more tables or views. And then, on the Select Source Tables and Views page, in the Source column, select the source worksheets and named ranges.

    • To employ an unnamed range that you specify with its address, on the Specify table re-create or query folio, select Write a query to specify the data to transfer. Then, on the Provide a Source Query page, provide a query similar to the following example:

                            SELECT * FROM [Sheet1$A1:B5]

  • When you're exporting to Excel, do one of the post-obit things:

    • To use a worksheet or a named range, on the Select Source Tables and Views folio, in the Destination column, select the destination worksheets and named ranges.

    • To use an unnamed range that y'all specify with its accost, on the Select Source Tables and Views page, in the Destination column, enter the range in the following format without delimiters: Sheet1$A1:B5. The wizard adds the delimiters.

Later you select or enter the Excel objects to import or export, you tin can also exercise the following things on the Select Source Tables and Views page of the wizard:

  • Review column mappings betwixt source and destination by selecting Edit Mappings.

  • Preview sample data to make sure information technology's what you expect by selecting Preview.

Issues with information types

Data types

The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted equally 255-character Unicode strings (DT_WSTR). SSIS maps the Excel data types as follows:

  • Numeric - double-precision float (DT_R8)

  • Currency - currency (DT_CY)

  • Boolean - Boolean (DT_BOOL)

  • Appointment/fourth dimension - datetime (DT_DATE)

  • String - Unicode string, length 255 (DT_WSTR)

  • Memo - Unicode text stream (DT_NTEXT)

Data type and length conversions

SSIS does non implicitly convert data types. As a result, you may take to utilize Derived Column or Information Conversion transformations to convert Excel data explicitly before loading it into a destination other than Excel, or to catechumen data from a source other than Excel earlier loading it into an Excel destination.

Here are some examples of the conversions that may be required:

  • Conversion between Unicode Excel string columns and non-Unicode cord columns with specific codepage.

  • Conversion between 255-character Excel cord columns and cord columns of different lengths.

  • Conversion between double-precision Excel numeric columns and numeric columns of other types.

Tip

If y'all're using the Import and Export Wizard, and your data requires some of these conversions, the wizard configures the necessary conversions for y'all. As a result, even when you want to apply an SSIS parcel, it may exist useful to create the initial package by using the Import and Export Wizard. Let the magician create and configure connectedness managers, sources, transformations, and destinations for y'all.

Bug with importing

Empty rows

When you lot specify a worksheet or a named range every bit the source, the driver reads the face-to-face block of cells starting with the first not-empty cell in the upper-left corner of the worksheet or range. As a consequence, your information doesn't accept to beginning in row 1, merely y'all can't have empty rows in the source data. For example, you can't have an empty row between the cavalcade headers and the data rows, or a title followed past empty rows at the top of the worksheet.

If there are empty rows higher up your data, you can't query the data every bit a worksheet. In Excel, you accept to select your range of data and assign a proper noun to the range, and then query the named range instead of the worksheet.

Missing values

The Excel commuter reads a certain number of rows (by default, eight rows) in the specified source to gauge at the data type of each cavalcade. When a cavalcade appears to comprise mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns zippo values for cells that incorporate information of the other blazon. (In a necktie, the numeric type wins.) About cell formatting options in the Excel worksheet do non seem to bear upon this data type determination.

You lot tin can modify this behavior of the Excel driver by specifying Import Style to import all values every bit text. To specify Import Way, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

Truncated text

When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does non observe any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string cavalcade instead of a memo column. Therefore, values longer than 255 characters may be truncated.

To import data from a memo column without truncation, you have two options:

  • Make certain that the memo column in at least one of the sampled rows contains a value longer than 255 characters

  • Increase the number of rows sampled by the driver to include such a row. You lot can increment the number of rows sampled by increasing the value of TypeGuessRows under the following registry key:

Redistributable components version Registry key
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Role\sixteen.0\Admission Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Issues with exporting

Create a new destination file

In SSIS

Create an Excel Connection Manager with the path and file name of the new Excel file that you desire to create. Then, in the Excel Destination Editor, for Name of the Excel sheet, select New to create the destination worksheet. At this point, SSIS creates the new Excel file with the specified worksheet.

In the SQL Server Import and Export Wizard

On the Choose a Destination page, select Browse. In the Open dialog box, navigate to the folder where y'all want the new Excel file to exist created, provide a name for the new file, and then select Open.

Consign to a large enough range

When y'all specify a range as the destination, an mistake occurs if the range has fewer columns than the source information. However, if the range that yous specify has fewer rows than the source data, the wizard continues writing rows without fault and extends the range definition to match the new number of rows.

Consign long text values

Earlier you can successfully save strings longer than 255 characters to an Excel cavalcade, the driver must recognize the data type of the destination cavalcade as memo and not string.

  • If an existing destination tabular array already contains rows of information, and so the first few rows that are sampled by the commuter must contain at least 1 case of a value longer than 255 characters in the memo cavalcade.

Related content

For more than information nearly the components and procedures described in this article, meet the following articles:

About SSIS

Excel Connectedness Manager
Excel Source
Excel Destination
Loop through Excel Files and Tables past Using a Foreach Loop Container
Working with Excel Files with the Script Chore

About the SQL Server Import and Consign Wizard

Connect to an Excel Data Source
Get started with this simple example of the Import and Export Wizard

Import data from Excel to SQL Server or Azure SQL Database