The Excel 2007 default output format for the SSIS Excel Destination is Excel Binary Format (.xlsb). If you’re creating a new Excel file, and have given it an .xlsx extension, you’ll get the following error when trying to open it in Excel:
Excel cannot open the file ‘xxx.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
If you want the Excel Destination to output a standard .xlsx file (Excel XML Format), you’ll need to tweak your Excel Connection Manager’s connection string property.