none
problem with retreving a excel data through excel source component.

    Question

  •  

    Hello,

     

    I have a problem with retreving a excel data through excel source component.

     

    I have source component as Excel Source which will connect to my .xls sheet.

    To retrieve the values from the sheet i am using a query as,

    "SELECT   F14,F3  FROM  [Charac Defn & Assgnment$]"

    The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..

    While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as

     

    TITLE: Microsoft Visual Studio
    ------------------------------

    There was an error displaying the preview.

    ------------------------------
    ADDITIONAL INFORMATION:

    Undefined function 'Convert' in expression. (Microsoft JET Database Engine)

     

    Is there any other function to change the format of the cell or i need to some thing else

    Please help me how to solve this issue.

    Tuesday, September 18, 2007 2:58 PM

Answers


  • Hello,

    The problem is due to the provider : He analyse only the first 8 lines to determine the datatype of each column.
    You can fin the solution on this blog :
    http://devselekta.blogspot.com/2007/09/ssis-excel-data-source-values-returned.html

    Tuesday, October 02, 2007 9:35 AM

All replies

  • Why don't you try formatting the cell as text in excel and then change the data type if needed in the data flow.

    Tuesday, September 18, 2007 3:01 PM
  • Or try to save the file as csv file, then use flat file source to import the csv file into your ssis package.

    Tuesday, September 18, 2007 6:39 PM
  •  Dhivya.S wrote:

     

    Hello,

     

    I have a problem with retreving a excel data through excel source component.

     

    I have source component as Excel Source which will connect to my .xls sheet.

    To retrieve the values from the sheet i am using a query as,

    "SELECT   F14,F3  FROM  [Charac Defn & Assgnment$]"

    The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..

    While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as

     

    TITLE: Microsoft Visual Studio
    ------------------------------

    There was an error displaying the preview.

    ------------------------------
    ADDITIONAL INFORMATION:

    Undefined function 'Convert' in expression. (Microsoft JET Database Engine)

     

    Is there any other function to change the format of the cell or i need to some thing else

    Please help me how to solve this issue.

     

    Hi Dhivya,

     

    SSIS considers the majority type among your values. Since you must be having more number of string data in the column F14, it is considering it as a varchar column and thus ignoring the numeric data.

     

    Do you really want the data to be uploaded into the destination table like this?

     

    In this case, a single quotation (') needs to be prefixed to each numeric value so that it is considered as a string.

    Please do that and it should work now.

     

    Regards,

    B@ns

     

    Tuesday, September 18, 2007 6:41 PM

  • Hello,

    The problem is due to the provider : He analyse only the first 8 lines to determine the datatype of each column.
    You can fin the solution on this blog :
    http://devselekta.blogspot.com/2007/09/ssis-excel-data-source-values-returned.html

    Tuesday, October 02, 2007 9:35 AM
  • Include the IMEX=1 in the ConnectionString property  of the Excel Connection. I hope this will solve your problem;
    • Proposed as answer by Anoop Sihag Friday, June 26, 2009 7:22 AM
    Friday, June 26, 2009 7:20 AM