Single quote is appended to all strings when data is transformed to excel when using Excel destination data flow component in SSIS package.
In this blog, I am going to discuss on the issue that one might face while transferring data from flat file or database to Excel i.e They see single quote appended to all strings when data is transformed to excel when using Excel destination data flow component. This is not the issue with latest Office product but it was an issue with old Office product.
Assume you have to develop an SSIS package that transfers data from SQL data source to Excel file, the excel file in turn is used by another application.
SSIS package is developed with OLEDB Source which fetches text data from SQL data source and data conversion is used and finally destination Excel data flow component is used to save the data.
While creating excel as destination, an Excel Connection manager is used to specify the path, name of file along with version.
If you have a machine with only Office 2003 installed, then in connection manager you would get option to select the Excel version as "Microsoft excel 97-2003". When this version is selected and data is transferred to Excel, data will be transferred without any issue but you would see Single quote appended to all strings in the excel sheet. This becomes an issue when excel is used in another application or used in any other processing.
When "Microsoft excel 97-2003" is used to create Excel connection manager, it create an connection manager using "Microsoft.Jet.OLEDB.4.0" provider and thus you would see Single quote is appended to all strings in the excel sheet.
One can resolve this issue by installing the latest or at least Office 2007 on their machine and should use "Microsoft excel 2007" as a Excel version, this create a connection manager using "Microsoft.ACE.OLEDB.12.0" provider and thus you will not see and single quote appended to all the strings in excel sheet.
Office 2003 product is not supported and its support life cycle has been expired, please upgrade your Office to latest versions.
Microsoft Support Life cycle of Office 2003 can be found at the link below https://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=office+2003&Filter=FilterNO
Hope is blog helps to solve and do not forget to upgrade to latest Microsoft products to get better functionality.
Author : Archana(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft
Comments
Anonymous
October 29, 2014
if i have the latest version of office (like 2010), what connection string should i use for excel? Many sites are showing provider "Microsoft.ACE.OLEDB.12.0.".Anonymous
June 21, 2015
I want data in sorted order when export into Excel. I am using Office 2003. We have office 2003 on production server and we can not change it. I have one application which required sorted excel data which are going to export using Excel destination. But when I am providing excel file to that application I am facing duplication of rows. how can I sort data of excel file in SSIS. Or any other way to remove single quote of string columns in SSIS package. When I am copying query result from SQL server into seperate excel file manually and proving it to my client application , its take that file and processed it but exporting using SSIS package is not working plz help me.Anonymous
March 30, 2016
Followed the instructions exactly. Still getting single quote appended to all the columns, even the ones defined in the spreadsheet template and in the excel destination as integerAnonymous
May 02, 2017
I have office 2010 installed and this is still an issue.