Data conversion errors in export to Excel SSIS package

0
votes

Building a package to pull data from SQL Server view and load it into an excel file. Getting a generic error (Multiple-step OLE DB operation generated errors... No work was done. Apparently this is usually from data type mis-matches. I am looking over the mapping in the Data Conversion task, but I don't see anything wrong. When I tried creating this in the Data Export wizard in SQL Server, I was getting truncation errors.

I have heard that I can use the "Derived Columns' task to fix the conversion failures, but I need to identify which columns are having trouble. How do I pinpoint the exact problem columns?

EDIT - Using BIDS 2008, exporting to an excel destination. I first tried with the import/export wizard in SSMS but kept failing, so I am now trying to do it in SSIS/BIDS.

I am currently getting the 0x80040E37 when I try to open the mapping tab in the Destination task. Apparently the fixes are to set to 32 bit or fix the mappings. I am running in 32 bit and I can't fix the mappings because the tab wont open because of this error.

1
sql-serverssisexport-to-excel
Which version of SSIS? Does "Export to Excel" mean you exporting to a flat file (CSV) or to an actual Excel Destination in your Data Flow Task? Does "Data Export Wizard" mean the Import Export Wizard, so you're not using BIDS or Server Data Tools? - Bacon Bits
One way to find the problem column is to add them to your dataflow one at a time. When you find the column, query for the maximum length of the data in that column, and verify that you are exporting to that size column in the dataflow. - Tab Alleman
I added a data viewer and found 70% of the columns were showing <long text> instead of data. I have been changing the mappings inthe data conversion and am down to 3 columns. In the data base they are one nvarchar(max) and 2 varchar(max). I have tried text stream dt_text and text stream dt_ntext but niether works. Thoughts? - BattlFrog
Is this the same issue/package you are asking about here, and is this question now obviated? stackoverflow.com/questions/29931411/… - Tab Alleman
I made two different packages to do the same job, trying different approaches and both are still failing for different reasons. So if possible, I would like to keep both questions going until one of the packages works. - BattlFrog

1 Answers

0
votes

The problem was Excel 2007 has a 255 length limit on fields. Two choices are to either switch your destination file type to 97-2003, or add a derived column task to truncate (substring) the field, which looses data. There is probably another option having to do with error handling and dumping the row off to a flat file or something.