In this blog post, I would like to address one typical limitation in SSIS Data Flow task which many of us might have experienced when you export the data to Excel 2007 (xlsx) Destination which has also been reported in the following connect item
https://connect.microsoft.com/SQLServer/feedback/details/477970/excel-data-types-from-ssis
It is actually a limitation of ACE Provider in the way it detects datatype for the excel destination thereby not preserving the datatype from the source.
In order to explain the issue, let me take a simple repro of the issue as shown below.
- Consider a simple Data Flow task as shown below
- For this repro, I have a used an OLEDB Source as SQL Server, master database with following query
select name,crdate
from sysdatabases
- I have used Multicast Operator since I wanted to broadcast the output to two Excel Destinations viz Excel 2003 Destination v/s Excel 2007 Destination.
- I have created 2 Excel Destination to compare the output for Excel 2003 Destination (xls) format as compared to Excel 2007 Destination (xlsx) format.
When I open the Excel 2003 File, the output is as shown below (which is the expected and desired outcome).
When I open the Excel 2007 File, the output is as shown below (which is unexpected and undesired)
This can be annoying since one has format when you large number of columns of the type date, number, currency etc. For the logical prespective, an obvious question would how does it work for Excel 2003 format and why it doesn’t work for Excel 2007 format which is supposed to be an advanced version. What has changed ?
The change is, the former use Jet Engine provider to export to the data to excel which has the ability to detect and preserve the data type from the source to the excel destination whereas the latter uses ACE Provider to export the data which uses different logic to detect the data type suitable for Excel destination and hence the issue.
If the Excel 2007 Destination File has atleast one row in the file, the ACE provider detects the data type of the Header row and uses the same datatype to populate the rest of the rows in the file. We can use this behavior of ACE Provider to work around the issue.
So what options we have to work around the issue.
- If suitable, use Excel 2003 Destination to produce an xls file.
- Pre-create an excel destination file with a header row with dummy data and correct identified data types which serves a template for the ACE Provider for the rest of the rows. You can hide the first row
You can right click on the dummy row and click Hide to hide the row from the end user
If hiding of the row doesn’t appeal you, you can use Execute SQL Task following the Data Flow Task which loads the Excel destination to update the header row will null values after populating the data into the Excel Destination as shown below
One obvious thought would be, rather than updating it to null value, shouldn’t we delete the header rows using delete statement in Execute SQL Task. However Delete SQL statement is not supported for Excel connection type as mentioned in the following KB
http://support.microsoft.com/kb/257819
However still if updating the header row to null values doesn’t convince, you can use the following visual c# code in SSIS Script task, to delete the header row.
However the following code references the Excel Interop dlls which are available by installing Primary Interop assemblies for Excel but it inturn requires you to install Office on the server (which may or may not be feasible for everyone)
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
public void Main()
{
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlWorkBook = xlApp.Workbooks.Open("C:/Users/pariks/Documents/ExcelExportIssue.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[1];
Excel.Range rng = xlWorkSheet.UsedRange;
((Excel.Range)xlWorkSheet.Rows[1]).EntireRow.Delete(Excel.XlDirection.xlUp);
rng.EntireRow.Delete(Excel.XlDirection.xlUp);
xlWorkBook.SaveAs("C:/Powerpivot/ExportExcelIssueResolved.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
}
Hope this helps !!!
Parikshit Savjani
Premier Field Engineer