none
SSIS 2005 - Export Data into an Excel 2007 Table

    Question

  • Hello !

    I have an excel file (xlsx) containing a table :

    Excel Table

    Once I launched my ssis task (successfully) to insert data in it, it is actually append after the table :

    Excel Table after the SSIS task

    The expected result/behavior:

    Expected

     

    So I am looking for a way to insert into the table and expand it with the data. I hope someone could help me.

    Thank you !

    • Edited by JulioGu Friday, October 15, 2010 1:10 AM
    Thursday, October 14, 2010 3:21 AM

Answers

  • @Nitesh Rai : I can't see named range because I am using the ACE OLE DB 12.0 driver since it is the only way (I found) to open an XML excel file (xlsx) ... :(

    I have created a connection using "Office 12.0 Access Database Engine OLEDB Provider" and I am able to see the range in the destination. Check this: http://dataintegrity.wordpress.com/2009/10/16/xlsx/ 

     


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by JulioGu Monday, October 18, 2010 1:51 AM
    Friday, October 15, 2010 2:50 AM
  • yes, i know, it doesnt work :) Remove the table, use named range.
    I really need it to be a table since I have Pivot Tables and Charts linked to it.


    Well, you can link Pivot table to a range also. Julio, i've tried many tricks to accomplish this but remember, SSIS is only a tool for Extraction, Transformation and Integration which is not (and no need to be) capable of excel operations unless you use programatically fill your table. And besides, the main reason that you can not do it, is because of Driver and Excel itself i think, but not SSIS.

     And for your requirement, i still didnt get the core idea behidn it. You can link your Pivottables and Charts to a data area without using table object. What makes you to not to dump the data to a regular sheet and but to use table object? Please explain in detail, so we can maybe suggest an alternative.


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me
    • Marked as answer by JulioGu Monday, October 18, 2010 1:51 AM
    Friday, October 15, 2010 12:17 PM
  • yes, i know, it doesnt work :) Remove the table, use named range.
    I really need it to be a table since I have Pivot Tables and Charts linked to it.


    Well, you can link Pivot table to a range also. Julio, i've tried many tricks to accomplish this but remember, SSIS is only a tool for Extraction, Transformation and Integration which is not (and no need to be) capable of excel operations unless you use programatically fill your table. And besides, the main reason that you can not do it, is because of Driver and Excel itself i think, but not SSIS.

     And for your requirement, i still didnt get the core idea behidn it. You can link your Pivottables and Charts to a data area without using table object. What makes you to not to dump the data to a regular sheet and but to use table object? Please explain in detail, so we can maybe suggest an alternative.


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me

    After 2 weeks of researches, thank you!

    I thought Pivot Tables had to be linked to Tables!

    So finally:

    - I export the data in the worksheet. No more table.

    - The named range *must* use OFFSET function because even if the OLE DB driver expands a "normal" named range, refreshing pivot tables fails whereas it doesn't when the named range uses OFFSET.

    :-)

    • Marked as answer by JulioGu Monday, October 18, 2010 1:51 AM
    Monday, October 18, 2010 1:50 AM

All replies

  • Hi,

    Not sure what you mean by "a table" in excel, but to insert after the header (ie, Row 2 of your excel file), you need to move the highlight cell to A1 and save it, then run your ssis task.

    Hope this helps.

    ~ J.

    Thursday, October 14, 2010 4:31 AM
  • I dont want to be disappointing but i think it is not easy (or even possible) as you think.
    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me
    • Proposed as answer by Kalman Toth Thursday, October 14, 2010 3:05 PM
    Thursday, October 14, 2010 2:51 PM
  • Your example is not clear , please comeup with other examples and scenarios,

    Do you want to get rid of the 2nd empty empty row after appending?


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, October 14, 2010 4:24 PM
  • Select the excel table and give it a name (like creating a named range). Then use this rangename as excel table name (range name would be visible inside the  "Name of Excel sheet" drop down in excel destination editor) in the data flow task.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Thursday, October 14, 2010 5:37 PM
  • @Onur Omer Ozturk : Users Medals Users Medals Users Medals Users Medals Users Medals It doesn't work

    @Nik - Shahriar Nikkhah : I have added the result I expect.

    @Nitesh Rai : I can't see named range because I am using the ACE OLE DB 12.0 driver since it is the only way (I found) to open an XML excel file (xlsx) ... :(

     

    Friday, October 15, 2010 1:04 AM
  • @Onur Omer Ozturk : Users Medals Users Medals Users Medals Users Medals Users Medals It doesn't work

    @Nik - Shahriar Nikkhah : I have added the result I expect.

    @Nitesh Rai : I can't see named range because I am using the ACE OLE DB 12.0 driver since it is the only way (I found) to open an XML excel file (xlsx) ... :(

     

     


    yes, i know, it doesnt work :) Remove the table, use named range.
    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me
    Friday, October 15, 2010 1:51 AM
  • yes, i know, it doesnt work :) Remove the table, use named range.
    I really need it to be a table since I have Pivot Tables and Charts linked to it.
    Friday, October 15, 2010 2:47 AM
  • @Nitesh Rai : I can't see named range because I am using the ACE OLE DB 12.0 driver since it is the only way (I found) to open an XML excel file (xlsx) ... :(

    I have created a connection using "Office 12.0 Access Database Engine OLEDB Provider" and I am able to see the range in the destination. Check this: http://dataintegrity.wordpress.com/2009/10/16/xlsx/ 

     


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by JulioGu Monday, October 18, 2010 1:51 AM
    Friday, October 15, 2010 2:50 AM
  • yes, i know, it doesnt work :) Remove the table, use named range.
    I really need it to be a table since I have Pivot Tables and Charts linked to it.


    Well, you can link Pivot table to a range also. Julio, i've tried many tricks to accomplish this but remember, SSIS is only a tool for Extraction, Transformation and Integration which is not (and no need to be) capable of excel operations unless you use programatically fill your table. And besides, the main reason that you can not do it, is because of Driver and Excel itself i think, but not SSIS.

     And for your requirement, i still didnt get the core idea behidn it. You can link your Pivottables and Charts to a data area without using table object. What makes you to not to dump the data to a regular sheet and but to use table object? Please explain in detail, so we can maybe suggest an alternative.


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me
    • Marked as answer by JulioGu Monday, October 18, 2010 1:51 AM
    Friday, October 15, 2010 12:17 PM
  • yes, i know, it doesnt work :) Remove the table, use named range.
    I really need it to be a table since I have Pivot Tables and Charts linked to it.


    Well, you can link Pivot table to a range also. Julio, i've tried many tricks to accomplish this but remember, SSIS is only a tool for Extraction, Transformation and Integration which is not (and no need to be) capable of excel operations unless you use programatically fill your table. And besides, the main reason that you can not do it, is because of Driver and Excel itself i think, but not SSIS.

     And for your requirement, i still didnt get the core idea behidn it. You can link your Pivottables and Charts to a data area without using table object. What makes you to not to dump the data to a regular sheet and but to use table object? Please explain in detail, so we can maybe suggest an alternative.


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me

    After 2 weeks of researches, thank you!

    I thought Pivot Tables had to be linked to Tables!

    So finally:

    - I export the data in the worksheet. No more table.

    - The named range *must* use OFFSET function because even if the OLE DB driver expands a "normal" named range, refreshing pivot tables fails whereas it doesn't when the named range uses OFFSET.

    :-)

    • Marked as answer by JulioGu Monday, October 18, 2010 1:51 AM
    Monday, October 18, 2010 1:50 AM