June 16, 2011 at 4:58 am
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
June 16, 2011 at 6:48 am
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?
June 16, 2011 at 1:49 pm
When destination is excel. We need to make sure data type is always DT_WSTR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply