I am debugging an SSIS package that starts with SQL Server data, transforms it, and puts it into an Excel file. I do this frequently in my work, and have never encountered this issue before: no rows are written to the Excel file. I attached a data viewer to the precedence constraint that leads to the Excel destination, and I see hundreds of rows of data flowing to the destination. When the package finishes executing, I open the Excel file and there is no data!
2
votes
Check to see if the excel destination is configured to ignore errors.
– TMNT2014
That must have been it. I set the error configuration to fail the package on error, and data went into the spreadsheet. Seems strange, since there was no error! But I have learned to expect strange things from SSIS.
– Rhys
4 Answers
1
votes
1
votes
0
votes
Bear in mind that it can take some time (many minutes) from a data flow task appearing to have successfully completed in debug mode before the write to the Excel file is completed.
I just encountered a similar scenario when executing a data flow task with an Excel output, and found that even though the data flow destination block turned green in the debugger (normally a good indication that everything's finished for other file types, e.g. csv) there was nothing in the Excel file. It was only when I started waiting for this message in the Output window that I could successfully see the results:
Information: 0x4004300B at Export data for checking, SSIS.Pipeline: "component "Excel Destination" (5918)" wrote 265488 rows.
I guess there's some buffering going on somewhere in the final output stage.
My clue was that when running the package as a whole, everything in the data flow task turned green, but the package flow showed the data flow task as still yellow ("in progress") until it was really finished.
