Post reply

Reading data from a text file

  • December 6, 2011 at 5:59 am

    #251602

    Hi All,

    When SSIS loads data from the text file,are all fields read as string data types, even if they contain integers?Should we convert the data before loading?

    Please let me know your views

    Your help would be appreciated

  • December 6, 2011 at 6:40 am

    #1417316

    SSIS is reading a text file, so yes, it's text until it's implicitly or explicitly converted to a specific datatype.

    so if you had the SSIS task read the file and import it into a table, it will convert the string value into the appropriate datatypes of the target table; it will raise errors for anything that doesn't match. that's a core functionality of SSIS, so I don't see where you could do much as far as pre-datatyping it any better than what SSIS will do.

    Can you explain the issue a little better? maybe i missed the question.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 6, 2011 at 8:35 am

    #1417426

    Thank you Lowell for your response

  • December 6, 2011 at 8:52 am

    #1417439

    kk.86manu (12/6/2011)

    Hi All,

    When SSIS loads data from the text file,are all fields read as string data types, even if they contain integers?Should we convert the data before loading?

    Please let me know your views

    Your help would be appreciated

    I don't think Lowell's response tells the whole picture.

    When you create a flat file connection manager, you have at that point the opportunity to set the data types of the columns in the file (from the Advanced tab). So if the file contains an integer and you are inserting to a column which is of type integer, you should define it as an integer and then no data conversion task will be required.

    Of course, if your integer column contains any non-integer data, you are more likely to get get an error than you would if you leave it as the default (50-character string, most likely).

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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