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