Post reply

SSIS cannot convert from Excel to SQL Server 2005

  • June 6, 2006 at 9:17 am

    #172886

    Hi,

    I created a pretty simple package: I am loading data from Excel 2003 to SQL Server 2005. In Control Flow tab I have a Data Flow task, and in Data Flow tab I have Excel Source and OLE DB destination. Fields are mapped properly from Excel to SQL Server so integer data will go into SQL Srever table column with data type int, and Excel alpha-numeric fields are mapped to SQL Server columns with data-type of varchar().

    However when I execute the package, for every string column I get a message:

    "Error in Data Flow task [OLE DB Destination [33]]: Columns "Value" and "val" cannot convert between unicode and no-unicode string data type."

    How to fix it ? Any way to check in Excel whether it is Unicode or not ? And same for SQL Server, when I have created the table I just specified varchar(), no any unicode.

    Thanks,

    Mark

     

     

  • June 6, 2006 at 10:18 am

    #642075

    The character data in excel will be in unicode so you can either create the character data fields in sql server as unicode or you will need to add in a data conversion transform to change the columns in question to non-unicode strings

    hth

    David

  • June 7, 2006 at 6:50 am

    #642250

    The Excel Provider supplies fields as unicode. SSIS has no control over this. As David says, use a data conversion component to correct the problem.

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • June 7, 2006 at 11:54 am

    #642361

    Thanks David and Jamie for your help.

    I tested both methods and decided to re-create destination SQL Server table with nvarchar fields data-type (as opposed to varchar). Now it works fine.

    Thanks,

    Mark

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply