This article contains a complete example of SQL Server data export to Microsoft Excel and CSV files 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 export packages.
You may download example files also.
You may develop amazing Microsoft Excel applications for working with Microsoft SQL Server using database development skills only!
Visit www.savetodb.com, download and install SaveToDB Add-In for Microsoft Excel.
Connect to tables, views, and stored procedures, edit the data and save it back to a database.
Add features to your Microsoft Excel applications step by step configuring apps via SQL.
Table of Contents
- The Basics of SQL Server Integration Services
- Excel 2003-2016 and CSV Providers
- SQL Server Export to Excel 2003-2016
- SQL Server Export to CSV
- SSIS Packages Run using Command Line
- See Also
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-2016 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 packages.
- 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.
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.
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 may 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 an Excel workbook path and Extended Properties with the "Excel 12.0;HDR=YES" value for import to SQL Server from Excel, and the "Excel 12.0" value for export from SQL Server to Excel.
In the 32-bit Import-Export Wizard, you may 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.
The Flat File Source Provider is available by default and has 32-bit and 64-bit versions.
You may use three basic ways to run Import-Export Wizard:
- Run from SQL Server group in the Start Menu.
- Run from SQL Server Management Studio (Object Explorer, Database, Tasks, Export Data...).
- 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.
At this step we can choose the following providers:
- SQL Server Native Client.
- Microsoft OLE DB Provider for SQL Server.
- .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:
Description: Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.
Moreover, I cannot find a solution.
So SQL Server Native Client is the best choice.
For SQL Azure, we can make a design time connection to local SQL Server and then define a real connection string at runtime.
You can choose the Microsoft Excel provider and select Excel 2003 or Excel 2007-2016 version only if you run the 32-bit version of the Import-Export wizard.
You should run the 64-bit version of the Import-Export Wizard to export to Excel 2007-2016 if you have the 64-bit Microsoft Office installed. In the 64-bit version of the wizard only manual configuration of the ACE OLE DB provider available.
Fill-in Data Source and Extended Properties fields as shown below.
Pay attention that the "Excel 12.0" string is used but not "Excel 12.0;HDR=YES".
It is a good idea to test connection.
Select the first option to export data from a table or view.
Select the second option to export data from a query or stored procedure.
If a destination file still not exists, you may choose a new table as a destination.
In the Columns Mappings dialog box, you can see the Create destination table option.
The Drop and re-create destination table option does not work with Excel files.
The Create destination table mode works only with Excel 2003 and does not work with Excel 2007-2016.
Also, you should delete the existing destination file before the package run.
For Excel 2007-2016, you should use an existing workbook as a destination file. For Excel 2003 you can.
Also, you should create column names on the destination worksheet and fill in one row of test data at least.
If the destination worksheet does not contain test data, you get all types as VarChar.
This method works, but you get text data in all columns:
To avoid this issue provide test data before the Import-Export Wizard run.
The Wizard defines the right types when the table contains the test data:
The types are quite different for non-existing and existing files, but the both work fine.
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:
- 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.
Verify the choices and click "Finish" button.
SQL Server Export to SCV can be used as an alternative of exporting to Excel files.
The Wizard steps are quite similar to steps discussed above.
On the Choose a destination step you should select the Flat File provider.
Pay attention to Text qualifier field.
Without quote qualifier a result may be wrong. For example:
ID,Float,Datetime,Nvarchar 1,123.4567,2011-06-17 01:00:00,Hello, SQL Server!!! 2,,2011-06-17 01:00:00,Hello, Excel!
With quote qualifier the result is:
"ID","Float","Datetime","Nvarchar" "1","123.4567","2011-06-17 01:00:00","Hello, SQL Server!!!" "2","","2011-06-17 01:00:00","Hello, Excel!"
This result is not good too.
To fix this issue the export package using text editor and change the string
for desired columns.
The desired result:
ID,Float,Datetime,"Nvarchar" 1,123.4567,2011-06-17 01:00:00,"Hello, SQL Server!" 2,,,"Hello, Excel!"
The basic command for SSIS package run is
dtexec /file <package> /decrypt <package password>
where the package password is from the "Save and Run Package" 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:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
Also, we can modify SSIS package paramaters using /set PropertyPath;Value option.
The SQL Server/SQL Azure source connection string example:
/set \Package.Connections[SourceConnectionOLEDB].Properties[ConnectionString];\""Data Source=%server%;User ID=%username%;Password=%password%;Initial Catalog=%database%;Provider=SQLNCLI10;Auto Translate=false;\""
The Excel 2003 destination connection string example:
/set \Package.Connections[DestinationConnectionExcel].Properties[ConnectionString];\""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%file%;Extended Properties="Excel 8.0;HDR=YES";\""
The Excel 2007-2016 destination connection string example:
/set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];\""Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%file%;Extended Properties="Excel 12.0"\""
Pay attention that "Excel=12.0" is used as Extended Properties. Otherwise, you will get en error.
The CSV destination connection string example:
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 export-config.txt for source SQL Server/SQL Azure connection string configuration:
source=\Package.Connections[SourceConnectionOLEDB].Properties[ConnectionString];\""Data Source=ko7h266q17.database.windows.net;User ID=excel_user@ko7h266q17;Password=ExSQL_#02;Initial Catalog=AzureDemo50;Provider=SQLNCLI10;Auto Translate=false;\""
Let's create export-task.txt for SSIS packages configuration:
# Specify x64 platform for ExportExcel2007add.dtsx if Microsoft Office x64 installed # Destination SSIS_Package Password Type Platform Workbook_Template ExcelTest.csv ExportCSV.dtsx 123 csv x86 no ExcelTest1.xls ExportExcel2003.dtsx 123 jet x86 delete ExcelTest2.xls ExportExcel2003add.dtsx 123 jet x86 Test_Template.xls ExcelTest.xlsx ExportExcel2007add.dtsx 123 ace x86 Test_Template.xlsx
If Workbook_Template is specified the command file copies the template to a destination file before the package run.
If the Workbook_Template column contains the delete value, the command file deletes the destination file before the package run.
Here the code of the export-ssis.cmd command file:
@echo off rem Check and change the paths to dtexec.exe if needed set dtexec_x64="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" set dtexec_x86="C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" if not exist %dtexec_x86% set dtexec_x86=%dtexec_x64% rem Reads %source% variable from config.txt for /F "eol=# tokens=1* delims==" %%i in (export-config.txt) do set %%i=%%j rem Reads and parses lines of task.txt for /F "eol=# tokens=1* delims==" %%i in (export-task.txt) do call :RUN_ONE %%i %%j %%k %%l %%m %%n %%o %%p %%q goto END :RUN_ONE set name=%1 set file=%~f1 set package=%2 set password=%3 set type=%4 set platform=%5 set template=%6 rem The connection string for the destination file set destination= if .%type%.==.csv. set destination=\Package.Connections[DestinationConnectionFlatFile].Properties[ConnectionString];%file% if .%type%.==.jet. set destination=\Package.Connections[DestinationConnectionExcel].Properties[ConnectionString];\""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%file%;Extended Properties="Excel 8.0;HDR=YES";\"" if .%type%.==.ace. set destination=\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];\""Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%file%;Extended Properties="Excel 12.0"\"" if .destination.==.. goto END rem The dtexec for required platform set dtexec= if .%platform%.==.x86. set dtexec=%dtexec_x86% if .%platform%.==.x64. set dtexec=%dtexec_x64% if .dtexec.==.. goto END rem Delete an old workbook if specified if .%template%.==.delete. if exist %file% del %file% > nul rem Makes a new workbook using a template if exist %template% copy %template% %file% > nul echo. echo %package% to %name% echo. %dtexec% /file %package% /decrypt %password% /set %source% /set %destination% /reporting E > %package%.log type %package%.log :END
The download package includes all these batch and example files.
If you have SQL Server Standard or higher, you can use SQL Server Integration Services to export SQL Server data to Microsoft Excel 2003-2016 or CSV files.
Hope that the article tips help you to save your time.