Loading Data from Text file to SQL Server

  • Hi All,

    I have a text file with one column where some of the data is blank where some of the records have some values like 0.9, -2.3,3.0,4 etc. I am loading that to one Table in SQL Server 2008. For those records where there is blank(empty) it gets loaded as 0 in my Sql server table.

    What is the work around with this? I do not want that to be loaded as 0. I want that to be NULL or someother number etc.

  • Also, my source text files are on my local machine and not on server. I cant copy that to actual server. So, Bulk Insert wont work.

  • Use Import/Export wizard


    Madhivanan

    Failing to plan is Planning to fail

  • I used Import Export wizard and that doesnt solve problem. SQL Server 2008 has this problem, it was working well if you use dtswiz with 2000/2005.

  • Bulk Insert will work fine if the server can see a "share" on your local machine.

    So far as the "0'/Null thing goes... load it into a staging table and change it. You should never load external data directly into a final table for a lot of reasons... mostly because you don't know what the condition of the external data is and on the fly validation is quite expensive performance wise. It's a real killer if a ROLLBACK happens to occur.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi,

    When you complete IMport Ecport Wizard, it asks you to save the SSIS package. Check that option.

    Open this SSIS Package in Business Intelligence Studio. Right click on "SourceConnection"->Edit. Go to the "Advanced" option on left hand side. it will give you list of columns in text file. Check the column properties, change the data type to string.

    I think it will work..

    Thanks,

    Rupashri

  • One more thing you can try..

    go to Source Data Flow tasks and open it.

    It has property to Retain Null Values.

    Thanks,

    Rupashri

  • Thanks for the help... it worked...

  • apat (7/31/2009)


    Thanks for the help... it worked...

    It worked? Which of Rupashri's SSIS suggestions worked?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 9 (of 9 total)

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