Hi there, I am facing a problem while converting data from Excel 2007 to SQL server 2005 table. I am using BIDS 2005.I have an excel file where one particular column has more than 255 characters. I use OLEDB connection for excel file as there is no driver for Excel 2007 in BIDS2005. I am using Microsoft Office 12.0 Access Database Engine OLE DB Provider for Excel file. Next, I changed advanced properties for the column to DT_NTEXT. But when I am getting errors on execution. They are: [OLE DB Source [1949]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. [OLE DB Source [1949]] Error: Failed to retrieve long data for column "action". [OLE DB Source [1949]] Error: There was an error with output column "action" (2046) on output "OLE DB Source Output" (1959). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [1949]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "action" (2046)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "action" (2046)" 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. Please advise on how can I deal with columns having more than 255 characters in Excel file. Thanks!

what is your source type? did you used EXCEL SOURCE?http://www.rad.pasfu.com

No, I did not. I am using OLE DB Source. I don't think I can use EXCEL Source for EXCEL 2007 file in BIDS 2005. Thanks!

I used following link for creating Source connection: http://dataintegrity.wordpress.com/2009/10/16/xlsx/

I think you mixed something up, and you are actually using the Jet driver. Please see this post that may help to resolve your issue: http://sqlblog.de/blog/2009/04/ssis-excel-import-column-data-types/Arthur My Blog

ArthurZ, ohh, I see. So, what Source connection shall I use for Excel 2007 file? Microsoft Jet OLEDB 4.0 ? I was using 'Microsoft Office 12.0 Access Database Engine OLE DB Provider' Thanks!

Hi sqlninja, you can use Excel Source as Reza stated above or surely the Jet OLEDB 4.0Arthur My Blog

Here is what your connection string should look like for excel source Provider =Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\test.xls;Extended Properties="EXCEL 8.0;HDR=YES";http://sqlworkday.blogspot.com/

what happen if your leave the output column of oledb source as DT_WSTR ? I want to find out that is this problem related to data provider or not.http://www.rad.pasfu.com

Reza, When using DT_WSTR, I was getting truncation error. As you mentioned above that I can use Excel source. But I do that, it doesn't recognize .xlsx extension files. It gives me error stating "File path contains invalid Excel file. Please provide file with .xls extension. " I am not sure if I am missing something here.. BIDS 2005 does not let .xlsx file ( Excel 2007 file) to be used with Excel Source.

you are right , the excel source in BIDS 2005 don't support excel 2007, what happen if you use Jet provider in oledb source as others mentioned?http://www.rad.pasfu.com

Now, I created JET 4.0 connection manager with extended properties and path pointing to Excel 2007 file. The connection is fine. But when I use Excel Source and select Excel file, I get error for invalid file extension. Extended properties : Excel 12.0;HDR=YES;IMEX=1 I tried Excel 8.0 as well.

did you used oledb source with Jet provider? ( don't use excel source in this case) let me know what is your problem when you use oledb source with Jet 4.0 provider?http://www.rad.pasfu.com

When I use OLE DB source, Select JET 4.0 OLE DB connection manager, Data access mode - Table or view. I get error: Could not find installable ISAM.

OK, let try last thing, go on your first way to set oledb source with access 12 provider, like what shown here: http://www.bidn.com/blogs/DevinKnight/ssis/504/using-excel-2007-in-ssis-2005 then for finding error rows, configure error output in oledb source and redirect them to a flat file destination , let us know if your package works with these chagnes?http://www.rad.pasfu.com

The office DLLs need to be re-registered (did you tinker with the MS Office installation)? Please refer to http://support.microsoft.com/kb/209805 article.Arthur My Blog

I'll try it. Meanwhile, I took a sample of data from Excel 2007 file and created a Excel 2003 file. this way I was able to EXCEL SOURCE. Next, I changed datatypes from DT_WSTR to DT_NTEXT. I changed datatypes at two places in Advanced Editor's Input and Output Properties; External Columns and Output Columns. When I do this, I get an error icon on EXCEL SOURCE which says "The component is not in a valid state. The validation errors are: Error at Data Flow Task [Excel Source [1]]: The output column "action" (95) on the error output has properties that do not match the properties of its corresponding data source column. Do you want the component to fix these errors automatically?" Fixing this error means, column "Action" has different datatypes at External Columns and Output columns. Link you posted earlier: http://www.justintubbs.com/code-samples/ssis-excel-source-failed-to-retrieve-long-data.php says "Your best bet is to convert those fields to DT_NTEXT datatype for both the "External Columns" and "Output Columns" within the Excel Source Advanced Editor." My point is Keeping 2007 & BIDS 2005 issue apart, using Excel 2003 file, I am getting same error when I change the datatype of column to DT_NTEXT.

don't change external column data type, just change output column datatype. let us know resulthttp://www.rad.pasfu.com

I kept External Column datatype same. Only changed output Column.I am getting same error :( [Excel Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. [Excel Source [1]] Error: Failed to retrieve long data for column "action". [Excel Source [1]] Error: There was an error with output column "action" (94) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE". [Excel Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "action" (94)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "action" (94)" 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. The column "Action" is the one causing all issues. Below is sample of value contained in it. I have tried DT_WSTR length - 4000, DT_NTEXT so far. I am not sure if [value] in cell is causing any problem, special characters and all. Cell value : {"Received request to change general point of contact. Faxed forms back because the old point of ", " contact stated on the certification letter doesn't match our database and the certification ", "letter is missing the correct the correct language."}

Also, I have installed BIDS 2008 so, Now I am gonna try it using EXCEL source. Connection is no longer as issue. Only issue is column length & truncation of value.

did you CONFIGURE ERROR OUTPUT for excel source as I mentioned before? redirect errors to a flat file destination to find out is there any value which caused problem or not.http://www.rad.pasfu.com

I tried that but it is giving same error as above. The flat file is empty. No rows at all. This thing is going crazy. Anyway, Thanks Reza for all your help & suggestions. I am gonna try to take that cell [value] , create a excel file containing only it and uses SSIS. Lets see.

could you send your excel file to me , I can run a test on my side. you can mail this to my address: a dot raad dot g at gmail dot comhttp://www.rad.pasfu.com

Reza, I tried few other things. As I stated earlier, I created a excel file containing only that troubling column. I used REDIRECT row for truncation error. Here I am getting values in the flat file for values which are causing error.So, I have two set of destination one for success of package and second one when there is truncation error. In excel source, my column has datatype as DT_WSTR(4000). In OLEDB destination, it is nvarchar(max) ; flat file has it as DT_WSTR(4000). If I change EXCEL source datatype to DT_NTEXT, then I end up with same errors mentioned above. So, i guess next question is what to do with data in flat file. thanks!

sqlninja, can you tell us this: looking at the Excel data, are the first/top rows that long (longer than 255 chars)? If not try to place those that are longer than 255 to the top and then run your package. One more thing that I often recommend: use the SQL Server Data Import Export Wizard to load your Excel file and see if it can. Please report back to us.Arthur My Blog

It worked out well. :) I had to tweak and sort few of the EXCEL columns to avoid truncation error. I knew this EXCEL trick as first 8 rows decide datatype etc. But I was too much focussed on using DT_NTEXT and ntext as my datatypes that I didn't pay attention to other details. to make it work, I used DT_WSTR(4000) in EXCEL Source and nvarchar(max) in OLE DB Destination. Thanks ArthurZ & Reza for all the help!