How to export SQL Server 2005 rows to excel? Everytime I do it I get errors

Question

I have SQL Server 2005 and I am trying to export a table into an Excel file.

I right click on my database and click export. I go through the wizard and choose to export to excel then I choose my one table that I want to export and hit finished.

I get this

Messages

• Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA.
  (SQL Server Import and Export Wizard)

• Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (72)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (72)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

• Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - SchoolUsers" (61) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

• Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)

It seems to export one row and then it dies. I don't know why.

Answer 1

In my experience, these kinds of issues are almost always related to data type conversion. Click on the "Edit Mappings..." button on the page where you select your source table and review the datatypes for the destination columns.

Answer 2

I would export to csv, and then open that csv in Excel. Works for me. Whenever I tried to move data directly between Excel and SQL Server 2005 via wizards and SSIS, I was frequently encountering bugs. I don't do it anymore.