locked
excel 2007 to Sql server table. Column with more than 255 characters. RRS feed

  • Question

  • 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!

     

    Monday, January 24, 2011 5:54 PM

Answers

  • 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
    • Proposed as answer by ArthurZ Tuesday, January 25, 2011 6:33 PM
    • Marked as answer by MuditGupta Friday, January 28, 2011 6:52 PM
    Monday, January 24, 2011 7:10 PM
  • 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
    By: TwitterButtons.com
    • Proposed as answer by ArthurZ Tuesday, January 25, 2011 6:33 PM
    • Marked as answer by MuditGupta Friday, January 28, 2011 6:52 PM
    Tuesday, January 25, 2011 3:18 PM

All replies

  • what is your source type? did you used EXCEL SOURCE?

    http://www.rad.pasfu.com
    Monday, January 24, 2011 6:01 PM
  • 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!

    Monday, January 24, 2011 6:03 PM
  • I used following link for creating Source connection:

    http://dataintegrity.wordpress.com/2009/10/16/xlsx/

    Monday, January 24, 2011 6:03 PM
  • 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
    By: TwitterButtons.com
    Monday, January 24, 2011 6:09 PM
  • 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!


    Monday, January 24, 2011 6:17 PM
  • Hi sqlninja,

    you can use Excel Source as Reza stated above or surely the Jet OLEDB 4.0


    Arthur My Blog
    By: TwitterButtons.com
    Monday, January 24, 2011 6:23 PM
  • 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/
    Monday, January 24, 2011 6:26 PM
  • 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
    Monday, January 24, 2011 6:29 PM
  • 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.

     

     

    Monday, January 24, 2011 6:35 PM
  • 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
    Monday, January 24, 2011 6:37 PM
  • 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.

     

     

    Monday, January 24, 2011 6:52 PM
  • 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
    Monday, January 24, 2011 6:56 PM
  • 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.


    Monday, January 24, 2011 7:04 PM
  • 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
    • Proposed as answer by ArthurZ Tuesday, January 25, 2011 6:33 PM
    • Marked as answer by MuditGupta Friday, January 28, 2011 6:52 PM
    Monday, January 24, 2011 7:10 PM
  • 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
    By: TwitterButtons.com
    Monday, January 24, 2011 7:17 PM
  • 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.

     

    Monday, January 24, 2011 7:20 PM
  • don't change external column data type, just change output column datatype. let us know result

    http://www.rad.pasfu.com
    Monday, January 24, 2011 7:39 PM
  • 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."}

    Monday, January 24, 2011 8:26 PM
  • 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.
    Monday, January 24, 2011 8:30 PM
  • 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
    Monday, January 24, 2011 8:31 PM
  • 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.

    Monday, January 24, 2011 8:49 PM
  • 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 com

    http://www.rad.pasfu.com
    Monday, January 24, 2011 8:54 PM
  • 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!

     

    Tuesday, January 25, 2011 3:01 PM
  • 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
    By: TwitterButtons.com
    • Proposed as answer by ArthurZ Tuesday, January 25, 2011 6:33 PM
    • Marked as answer by MuditGupta Friday, January 28, 2011 6:52 PM
    Tuesday, January 25, 2011 3:18 PM
  • 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!

    Tuesday, January 25, 2011 5:59 PM
  • I found a great solution to limitation of excel when reading cells with a lot of text and when firts rows has no data!

    Look here: http://dataintegrity.wordpress.com/2009/10/16/xlsx/


    aaaa

    Monday, December 23, 2013 7:11 PM