none
export to excel question

    Question

  • Hi All,

    I was wondering if someone can advise what I'm doing wrong when trying to export data from a table that has over 335 columns.

    In my OLE source, I see all 335 columns.  In my data conversion, I see 335 columns. The excel 2007 file has all the columns.

    BUT in my Excel Source Destination, I only see 255 columns?  How can I see all the columns?

    Thanks




    • Edited by Red8Rain Tuesday, April 10, 2012 2:32 PM added correct columns being displayed
    Tuesday, April 10, 2012 5:11 AM

Answers

All replies

  • Have you mapped all the 335 columns in the Excel destination component?

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, April 10, 2012 5:15 AM
  • In the Excel destination component, when I'm in the Mapping tab, all the columns for the OLEDB Source:Column_Name are there.  But for Data Conversion:Column_Name, I don't see all my columns.


    • Edited by Red8Rain Tuesday, April 10, 2012 5:20 AM
    Tuesday, April 10, 2012 5:20 AM
  • BullsEye. Include the newly type casted columns in the downstream flow and map them to excel destination.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, April 10, 2012 5:21 AM
  • can you tell me how to do this as I'm confused :D .

    Tuesday, April 10, 2012 5:22 AM
  • Follow these resources:

    http://www.sqlbiinfo.com/2011/05/ssis-2008-data-conversion.html

    http://ericwisdahl.wordpress.com/2011/06/16/ssis-quick-tip-data-conversion-task-or-cast-in-source/


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, April 10, 2012 5:27 AM
  • I read those two links and it doesn't help me ...

    In the Excel Destination Editor Mappings' tab, in the available input columns, it has all the columns.  But in the available destination columns, it is missing columns.

    please see attachment/picture

    Tuesday, April 10, 2012 5:35 AM
  • OK.

    • Open the Excel file
    • Add the remaining column header label for all 335 columns
    • Save the file and Close Excel
    • Go to BIDS, refresh connection or if nothing works delete the existing excel connection manager and create a new manager

    You will see all columns available for mapping


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, April 10, 2012 5:43 AM
  • i'm sorry to say that that didn't work.  I have even tried creating a new project and everything but the 'available destination column' will not go pass the 'RefBuilding2States.'

    i have even tried new excel files and the column headers are all present in the file under sheet1$

    Tuesday, April 10, 2012 5:50 AM
  • Can you see all Excel columns in the Excel destination mapping page? Perhaps if you can attach another picture.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, April 10, 2012 5:52 AM
  • Sir, the Excel Destination mappings page, Available Destination Column only goes up to RefBuilding2States, while the Available Input Columns provide more after the Data Conversion:RefBuilding2States column.

    in the bottom portion, where it say Input Column and Destination Column, it also goes to RefBuilding2States.

    But as you can see from the Available Input Columns, I have highlighted columns that should be available in the destination as well.  Those columns are present in the Excel .xlsx file.

    Tuesday, April 10, 2012 6:02 AM
  • I just went into the Advanced Editor Screen and it seem that the input columns only goes up to RefBuilding2States.

    I think I should add the column manually in this screen?

    Tuesday, April 10, 2012 6:06 AM
  • Create a new excel connection manager for destination and not a new excel workbook. This is really absurd. There is no reason for SSIS not to show the remaining columns.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, April 10, 2012 6:09 AM
  • I have done that as well :D .  And yes it is absurd for this to be happening.  the new excel connect manager didn't work in existing project or a new project.

    Manually adding the column does not work as it isn't 'sticking' after I 'ok' out of the advanced editor.

    • Edited by Red8Rain Tuesday, April 10, 2012 6:15 AM
    Tuesday, April 10, 2012 6:12 AM
  • Delete the existing Excel manager and create a new manager. Make sure your excel has all 335 column header labels, before you configure the Excel manager. Have you tried this?

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, April 10, 2012 6:16 AM
  • i have tried all that before posting on the forum asking for help :D .  as I have mentioned, i have went as far as creating a whole new excel file and project

    The terrible thing is that SSIS leads me to believe there are no additional errors until run time, when I get the following, which I can't even modify:

    SSIS package "Import_QS.dtsx" starting.

    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

    Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "RefConductsEvents" (2335) on output "Data Conversion Output" (302) and component "Data Conversion" (300) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    ...

    ...

    Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.

    Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.

    Error: 0xC0202009 at Data Flow Task, Excel Destination [19]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

    Error: 0xC0202025 at Data Flow Task, Excel Destination [19]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

    Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "Excel Destination" (19) failed the pre-execute phase and returned error code 0xC0202025.

    Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "Excel Destination" (19)" wrote 0 rows.

    Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.

    Task failed: Data Flow Task

    SSIS package "Import_QS.dtsx" finished: Success.




    • Edited by Red8Rain Tuesday, April 10, 2012 6:37 AM
    Tuesday, April 10, 2012 6:16 AM
  • Do the columkns stop at 225 or 255?

    If 255, this indicates that the excel driver is the issue - pre 2007, excel could only have a max of 256 columns

    Check:

    1: That the xl connection type is 2007 and not 97-2003

    2: that the version of excel on the SSIS server is 2007 not 97-2003

    3: that the output you are pushihng to is not set up in "compatability mode"

    4: Check that your OLEDB driver is up to date


    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Tuesday, April 10, 2012 7:02 AM
  • Hi Geoff.

    I just counted again and you are correct, I can only see 255 columns.  I have updated my OP to show 255 columns.

    1. Yes the connection type is 2007

    2. Excel has to be installed on the SSIS server?  I have Excel 2010 installed on my desktop, which I'm using to create this SSIS package

    3. where can I check for compatability mode?

    4. once more, where do I check if my oledb driver is up to date? and is this on the server or the desktop i'm working on?

    Thanks

    Tuesday, April 10, 2012 2:35 PM
    • Marked as answer by Red8Rain Tuesday, April 17, 2012 10:03 PM
    Tuesday, April 17, 2012 10:03 PM
  • As we a limitaion on excel source and destination columns,  we can see only 255 columns in source/destinaton component.

    The solution for this is.

    For destination , Use a multicast transformatiom before the destination , then use the ragned query in the Excel destination component.

    1<sup>st</sup> OLE DB Destination: - Select * FROM [Sheet$A1:IU]

    2nd  OLE DB Destination: - Select * from [Sheet$IV1:IZ(or next 255 tab)]

    Both the destination component's should use same connection manager .

    Same can be used for sourec as well

    Friday, September 20, 2013 6:12 AM