generated excel from SSIS but getting quote in every column?
generated and excel from SSIS package successfully.
every column is having extra
' (quote) mark why is it so?
My source sql table is like below
Name price address ashu 123 pune jkl 34 UK
sql table i took
all column as
Excel Manager when it is going to create table
Excel Destination took all column as same
Data Flow I have used
Data Conversion transformation to prevent
unicode conversion error.
Please advice where i need to change to get the clear columns in excel file.
4 Solutions collect form web for “generated excel from SSIS but getting quote in every column?”
You could create a template Excel file in which you have specified all the column types (change to Text from General) and headers you will need. Store it in a
/Template directory and have copy it over to where you will need it from within the SSIS package.
In your SSIS package:
- Use Script Component to copy Excel Template file into directory of choice.
- Programatically change its name and store the whole filepath in a variable that will be used in your corresponding Data Flow Task.
- Use Expression Builder for your Excel Connection Manager. Set the ExcelFilePath to be retrieved from your variable.
the single quote or apostrophe is a way of entering any data (in Excel) and ensure it is treated as text so numbers with leading zeros or fractions are not interpreted by Excel as numeric or dates.
a NJ zip code for instance 07456 would be interpreted as 7456 but by entering it as ‘07456 it keeps its leading zero (please note that numbers in your example are left aligned, like text is)
I guess SSIS is adding the quotes because your data is of VARCHAR type
First, define the field types for your excel destination in SSIS, any non-text fields will format properly without the ‘. Then, add a derived column transformation between your source and destination, and use a replace statement for any text columns.
This caused me major problems! So I completed the following:
You can change the excel version to ‘Microsoft Excel 4.0’ within the excel connection manager in your SSIS package.
Then within excel follow Options > Trust Center > Trust Center Settings > File Block Settings > Untick the ‘Open’ checkbox for ‘Excel 4 workbooks’ and ‘sheets’.