Introduction

This article contains a complete example of Microsoft Excel and CSV data import to SQL Server using SQL Server Integration Services.

You will find useful tips to get around difficulties and save your time.

You will find a useful configurable command file to run import packages.

You can download example files also.

Table of Contents

The basics of SQL Server Integration Services

Excel 8.0;HDR=Yes;';'; public static DataTable GetDataTableFromExcelFile(string fullFileName, ref string sheetName) OleDbConnection objConnection =. Excel Planilha Receita Despesa e Saldo Hoja de Ingresos y Gastos de Excel Equilibrio Excel Sheet and Income Expense Balance Foglio Excel e Expense. BIFF 8 (.XLS) Excel 95 and above. Office Open XML (.XLSX Excel 2007 and above. Overall, PHPExcel allows you to read, write and create Excel documents in PHP. The only downside is the learning curve; it can be a tad tricky to use, especially for novices.By utilising tcPDF, DomPDF or mPDF libraries, which need to be installed. Excel add ins, automate excel, find duplicates in excel, excel utility, excel asap utilities, spaces remover in excel, clean data in excel 5 Automate Excel v.2.8.0 Business / Word Processing.

SQL Server Integration Services (SSIS) are powerful tools to import-export data between SQL Server/SQL Azure and other data sources including Microsoft Excel 2003/2007 and CSV files.

You can create SSIS packages using two tools:

• SQL Server Business Intelligence Development Studio (BIDS)
• SQL Server Import-Export Wizard.

You can run SSIS packages using the following tools:

• SQL Server Import-Export Wizard when you create a package.
• The dtexec command line utility.
• The dtexecui interactive utility.
• SQL Agent.

BIDS and SQL Agent are not avaiable in SQL Server Express Edition.
Also, in Express Edition, you cannot save a designed package from the Import-Export Wizard,

See a good video how to design an SSIS package using BIDS:
Creating a Basic Package (SQL Server Video)

We will talk about package design using SQL Server Import-Export Wizard.
This feature covers import-export task with Microsoft Excel and CSV files.

Excel 2003-2016 and CSV Providers

Jet OLE DB Provider for Microsoft Excel 2003

The Microsoft.Jet.OLEDB.4.0 Provider is used to import-export data with Microsoft Excel 97-2003 workbooks.

It is named 'Microsoft Excel' in the Import-Export Wizard.

This provider is available by default on all Windows platform but has only the 32-bit version.

So use the 32-bit version of dtexec utility and specify Run64BitRuntime=False in the Debug Options of SQL Server Business Intelligence Development Studio project properties.

ACE OLE DB Provider for Microsoft Excel 2007-2016

Microsoft Office 12.0 Access Database Engine OLE DB Provider is used to import-export data with Microsoft Excel 2007-2016 workbooks like *.xlsx, *.xlsm, *.xlb.

You can download and install this provider using the following link:

Note that the platform of the provider should be the same as installed Microsoft Office platform.

Also, you should use the appropriate platform versions of dtexec and the Import-Export Wizard.

So if you have Microsoft Office x64 installed you should install the 64-bit provider and use the 64-bit Import-Export Wizard.

Note that SQL Server Management Studio and SQL Server Business Intelligence Development Studio run only the 32-bit Import-Export Wizard from a shell. So use the 'Import and Export Data (64-bit)' link from the SQL Server Start Menu group to run the wizard.

To configure the provider fill in the Data Source value with Excel workbook path and Extended Properties with 'Excel 12.0;HDR=YES' value for import to SQL Server from Excel and 'Excel 12.0' for export from SQL Server to Excel.

In the 32-bit Import-Export Wizard, you can use the 'Microsoft Excel' data source with the 'Microsoft Excel 2007' version to configure the ACE OLE DB Provider the same way as for Excel 2003 data source.

Flat File Source Provider

The Flat File Source Provider is available by default and has the 32-bit and 64-bit versions.

SSIS Package Design using Import-Export Wizard

How to Run Import-Export Wizard

You have three basic ways to run Import-Export Wizard:

1. Run from SQL Server group in the Start Menu.
2. Run from SQL Server Management Studio (Object Explorer, Database, Tasks, Export Data..).
3. Run from SQL Server Business Intelligence Development Studio (Solution Explorer, SSIS Packages, SSIS Import and Export Wizard..).

Only the first way allows running the 64-bit version of the Wizard that is required for the 64-bit ACE OLE DB Provider.

Choose a Data Source as Excel 2003/Excel 2007/CSV

On this step, you can use a production path to a data source or a temporary development path.

In the last case, you can specify the path at runtime. Easy youtube downloader express.

In this example drive d: is used as a path directory.

Excel 2003 as Data Source

For importing from Excel 2003 workbooks, select Microsoft Excel as Data source and Microsoft Excel 97/2003 as Excel version.

Excel 2007-2016 as Data Source

For importing from Excel 2007-2016 workbooks using the 32-bit ACE OLE DB Provider, select Microsoft Excel as Data source and Microsoft Excel 2007 as Excel version.

For importing from Excel 2007-2016 workbooks using the 64-bit ACE OLE DB Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider.

Specify Extended Properties = 'Excel 12.0;HDR=YES' and Data Source on the All tab.

The good idea is to Test Connection on the Connection tab.

A CSV file as Data Source

Carefully specify the first three tabs, especially, the 'Advanced' tab.

Don't forget to check 'Column names in the first data row.'

It is a good idea to use 'Suggest Types..' button on the 'Advanced' tab.

Choose a Destination as SQL Server/SQl Azure

At this step we can choose the following providers:

1. SQL Server Native Client.
2. Microsoft OLE DB Provider for SQL Server.
3. .NET Framework Data Provider for SqlServer.

The only .NET Framework Data Provider for SqlServer allows connecting to SQL Azure at design time.

However, I have an error during the package run using dtexec utility: 'ADO NET Destination has failed to acquire the connection'. Moreover, I cannot find a solution.

SQL Server Native Client is the best choice, therefore.

For SQL Azure, we can make a design time connection to local SQL Server and then define a real connection string at runtime.

Specify Table Copy or Query

The copying from a table is just enough for Excel/CSV import.

Select Source Tables and Views

Microsoft Excel has two types of data sources:

• Sheets.
• Named ranges.

You can see 'ExcelTest' named range and 'Sheet1$' sheet on the screenshot.

Use named ranges if possible because users can add information on worksheets somewhere.

Save and Run Package

The SQL Server Express Import-Export Wizard does not allow saving packages.

There are two options for storing SSIS packages:

• SQL Server
• File system

Advantages of the storing to a file system are that you can edit SSIS packages using a text editor and distribute packages easily.

There are some options of the Package protection level:

Hugenote Kollege is a private Christian post-school training institution that specializes in the training of leaders in various ministries as well as professionals in the social welfare sector. Explore this cemetery for graves, information and tombstones for names in Huguenot Cemetery in New Paltz, New York, a Find A Grave Cemetery. HueNote is a text editor that can edit in parallel multiple notes. You can set the editor any number in the range of window size. Of course, you can also use in the editor only to one as a simple note-taking app. Hienote review.

• Do not save sensitive data.
• Encrypt sensitive data with the user key.
• Encrypt sensitive data with a password.
• Encrypt all data with the user key.
• Encrypt all data with a password.

User key options restrict SSIS package run on other machines. Thus, you cannot distribute it easily.

Encrypt all data options restricts access to an SSIS package text. Thus, you cannot edit it using a text editor.

If an SSIS package does not contain sensitive data, you cannot run and debug a package using SQL Server Business Intelligence Development Studio.

So, the best way for most cases is 'Encrypt sensitive data with password'.

Password '123' is used in the example.

Complete the Wizard

Verify the choices and click 'Finish' button.

Datetime/Float/Double/Null Issues of CSV Import

Import from CSV files has some issues:

• SSIS insert default values instead of null values.
• SSIS cannot convert float/double values without fixed scale from CSV files.

For example, the imported Float value is not equal to source value 123.4567, and the Datetime value has datetime value 1753-01-01 00:00:00 (which can be 1899-12-30 00:00:00 if a date type specified).

I know only the one solution for the float/double issue. It is to setup the decimal data type for CSV column using a scale that covers possible scales of column data.

There are two ways to turn on null values:

1. Change 'RetainNulls' property to true in an SSIS package using a text editor.
2. Open an SSIS package in SQL Server Business Intelligence Development Studio, activate the Data Flow tab, select the Flat File Source and set 'RetainNull' to True in the property window. Alternatively, click 'Edit..' in the Context Menu and check 'Retain null values from the source as null values in the data flow.'

The results with null values.

SSIS Packages Run using Command Line

SSIS Packages Runtime Configuration

The basic command for SSIS package run is

where a package password is from the 'SaveandRunPackage' step of the Import-Export Wizard.

On 64-bit computers, the 64-bit version of dtexec is used by default.

The paths for dtexec executables:

Also, we can modify SSIS package paramaters using /set PropertyPath;Value option.

The SQL Server/SQL Azure destination connection string example:

The Excel 2003 source connection string example:

The Excel 2007-2016 source connection string example:

The CSV source connection string example:

SSIS Packages Run Automation

The right technology to run SSIS packages is SQL Agent.

However, we can use a simple and configurable batch file to run SSIS packages.

Let's create import-config.txt for destination SQL Server/SQL Azure connection string configuration:

Let's create import-task.txt for SSIS packages configuration:

Here the code of the import-ssis.cmd command file:

The download package includes all these files.

Conclusion

If you have SQL Server Standard or higher, you can use SQL Server Integration Services to import data from Microsoft Excel 2003-2016 or CSV files to SQL Server.

Hope that the article tips help you to save your time.

Download

See Also

• References
• How-To
• Downloads

EVO Excel to PDF Converter can be used in any type of .NET application to convert Excel XLS and XLSX documents to PDF documents. The Excel to PDF Converter does not use Microsoft Office or other third party tools. The Excel document to convert can be in a memory buffer or in a file and the resulted PDF can be produced in a memory buffer, in a file on disk or in a stream.
The integration with existing .NET applications is extremely easy and no installation is necessary.

Dependencies

This package has no dependencies.

Used By

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

Excel 8.0

This package is not used by any popular GitHub repositories.

Excel 8.0

Version History

Excel 8.0 Release