Post reply

Phone Numbers in Excel being loaded as NULL to SQL Server

  • May 17, 2011 at 7:48 am

    #1325815

    Hey,

    Just gone through the Excel that you have posted. Your getting NULL which is obvious in your case. Its's behaviour of MS SSIS engine.

    SSIS engine, by defauly, scans 1st 8 rows of your excel file to guess at the data type of each columns. When a column appears to contain mixed data types especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.)

    In your case as majority of rows are blank under phone nos, so you are getting all rows returned as NULL.

    Solution: You might want add IMEX into your connection string prperty of Source Connection "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\...\New Microsoft Excel Worksheet.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";"

    see more details at BOL: http://msdn.microsoft.com/en-us/library/ms141683.aspx (Usages Consideration-> Missing value section)

    __________________________________________
    ---------------------------------------------------
    Save our mother Earth. Go Green !!!

  • May 17, 2011 at 7:51 am

    #1325819

    let me know if it's not resolving your problem..

    __________________________________________
    ---------------------------------------------------
    Save our mother Earth. Go Green !!!

Viewing 2 posts - 16 through 16 (of 16 total)

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