Post reply

Using SSIS : Data from excel to sql server

  • May 3, 2010 at 6:13 am

    #224091

    Hi,

    i have a excel file it having the alphanumeric data in the columns,

    like

    id value secondvalue

    231 string1 x

    abc String2 x

    123 String4 x

    when i see the preview for excel source

    it is showing the null value in id filed for "abc"

    how can i transfor data>

    🙂

  • May 3, 2010 at 7:00 am

    #1161228

    Hi,

    Can you try placing a Data Conversion task after the Source and convert the required fields.

    Thanks & Regards,
    MC

  • May 3, 2010 at 7:54 am

    #1161277

    This is a very, very, very common problem with SSIS and Excel. It shows up in hundreds of blog and forum posts.

    Have you tried Google?

    The issue is that Excel guesses the datatype based on a fixed number of rows of that column. If it sees x times a number, the column will get a numeric datatype, thus rendering NULL for your string value later on.

    How to fix this? Set IMEX=1 in your connection string. This will tell the driver that if there are intermixed datatypes, to convert everything to text. Furthermore, you have to set a registry value to 0, telling the driver to scan the whole column, and not just the first x rows.

    Reading material:

    http://support.microsoft.com/kb/189897 (registry key)

    http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/ (IMEX=1).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 3, 2010 at 11:49 pm

    #1161604

    hahaha....same question again

    Raunak J

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

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