generated excel from SSIS but getting quote in every column?

I have generated and excel from SSIS package successfully.
But every column is having extra ' (quote) mark why is it so?

enter image description here

  • Add unique constraint to combination of two columns
  • MySQL versus SQL Server Express Performance Comparison
  • CTE within a CTE
  • AcquireConnection method call to the connection manager <Excel Connection Manager> failed with error code 0xC0202009
  • Using GUIDs in Primary Keys / Clusted Indexes
  • SQL Server Raiserror not causing exception in .NET client
  • My source sql table is like below

    Name    price    address
    ashu    123      pune
    jkl     34       UK
    

    In my sql table i took all column as varchar(50) datatype.
    In Excel Manager when it is going to create table
    Excel Destination took all column as same varchar(50) datatype.

    And in 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:

    1. Use Script Component to copy Excel Template file into directory of choice.
    2. Programatically change its name and store the whole filepath in a variable that will be used in your corresponding Data Flow Task.
    3. 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.
    Should be:

    (REPLACE(Column1, "'","")
    

    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’.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.