none
Problem with Excel output

    Question

  • I'm being asked to export data to an excel spreadsheet. On each sucsessive run SSIS starts to write the data futher and further down the sheet so I wind up with rows and rows of empty records. I'm not seeing any kind of setting that says to overwrite the sheet or anything like that. What am I doing wrong?
    Friday, February 22, 2013 5:16 PM

Answers

  • Falcon,

    When we work with Excel + SSIS an empty row on excel is not so empty to SSIS, e.g.

    You write 10 lines on Excel, and then delete the content, keeping then blank, if this excel is a target of SSIS, the first input row from SSIS will be 11. To solve that, you must always create a new file and do not use the same.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marked as answer by falcon00 Friday, February 22, 2013 9:11 PM
    Friday, February 22, 2013 5:19 PM
  • Falcon,

    A totally new File!, Here i work with a similiar issue, i solved that creating a folder with the default file (If some line have to be harded, add it to that default file), and then copy from default folder to work folder.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marked as answer by falcon00 Friday, February 22, 2013 9:11 PM
    Friday, February 22, 2013 5:27 PM

All replies

  • Falcon,

    When we work with Excel + SSIS an empty row on excel is not so empty to SSIS, e.g.

    You write 10 lines on Excel, and then delete the content, keeping then blank, if this excel is a target of SSIS, the first input row from SSIS will be 11. To solve that, you must always create a new file and do not use the same.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marked as answer by falcon00 Friday, February 22, 2013 9:11 PM
    Friday, February 22, 2013 5:19 PM
  • When you say create a new file you mean a TOTALLY new file or can I get away with copying the old file? I'm asking because there is a header row that has to persist across all instances of this file.

    Friday, February 22, 2013 5:25 PM
  • Falcon,

    A totally new File!, Here i work with a similiar issue, i solved that creating a folder with the default file (If some line have to be harded, add it to that default file), and then copy from default folder to work folder.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marked as answer by falcon00 Friday, February 22, 2013 9:11 PM
    Friday, February 22, 2013 5:27 PM
  • Yup. That got it. Thanks.
    Friday, February 22, 2013 9:11 PM