Excel destinaton error: Cannot create OLE DB acccessor at Excel destination when using SSIS 2008

• hema_subhash

  Old Hand

  Points: 345

  More actions

  June 16, 2011 at 4:58 am

  #389748

  Hello everyone,

  I have been trying to create a simple SSIS package, that will call multiple stored procedures in MS SQL Server, and then populate the result sets sequentially into different tabs of a single excel file.

  Each SP is called in a separate DFT, and the results are populated into a excel tab, where the header columns are provided. Within the DFT there's a Data conversion task in between the OLE DB source and Excel destination, to convert non-unicode characters to Unicode.

  My package works fine with all the other results sets, except one DFT.

  I'm not doing anything different in this "error causing DFT". When I create the excel destination tab freshly, the DFT works fine the first time and populates the result set into the tab. But then when I re-run it keep getting the following errors:

  Error: 0xC0202009 at Clarification Report, Excel Destination 1 [164]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

  Error: 0xC0202025 at Clarification Report, Excel Destination 1 [164]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

  Error: 0xC004701A at Clarification Report, SSIS.Pipeline: component "Excel Destination 1" (164) failed the pre-execute phase and returned error code 0xC0202025.

  Just so you know, at the end of the DFTs, I have added two file system tasks that will copy a template file, and rename it as the excel destination.

  Please help!!

  Hema

• hema_subhash

  Old Hand

  Points: 345

  More actions

  June 16, 2011 at 6:48 am

  #1339626

  On further debugging it was found that the error occurred only when the excel destination has columns with the DT_NTEXT datatype. Once I replaced this data type with DT_WSTR the error disappears. I'm ignoring Truncation errors thus the package executes successfully.

  I can't allow truncation to happen and the excel destination therefore should have DT_NTEXT.. I'm using Excel 2007 and SSIS 2008...

  So is there anyway I can make it work with the DT_NTEXT data type?

• Smash125

  Hall of Fame

  Points: 3562

  More actions

  June 16, 2011 at 1:49 pm

  #1340060

  When destination is excel. We need to make sure data type is always DT_WSTR