CODEWITHSUNDEEP
sql-serversql-server-2005ssisexport-to-excel
chobo2
chobo2

Reputation: 85765

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

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.

Upvotes: 3

Views: 6238

Answers (3)

joinsaad
joinsaad

Reputation: 863

Excel 2003 - 2007 has a limit of 32767 characters as mentioned by Joe. I suggest to check the length of your data in your suspicious column using this command

select * from yourtable where len(yourcolumn) > 32767

You will get the list of all rows in which the length exceeds the required limit. Remove these rows from this table and then try to export. It should work fine.

Upvotes: 0

A-K
A-K

Reputation: 17080

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.

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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.

Upvotes: 4

Related Questions