Post reply

Loading Data from Text file to SQL Server

  • July 29, 2009 at 2:28 pm

    #212751

    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.

  • July 29, 2009 at 3:09 pm

    #1032038

    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.

  • July 30, 2009 at 4:19 am

    #1032258

    Use Import/Export wizard

    Madhivanan

    Failing to plan is Planning to fail

  • July 30, 2009 at 8:00 am

    #1032393

    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.

  • July 30, 2009 at 9:36 am

    #1032477

    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)

  • July 31, 2009 at 1:10 am

    #1032801

    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

  • July 31, 2009 at 1:13 am

    #1032804

    One more thing you can try..

    go to Source Data Flow tasks and open it.

    It has property to Retain Null Values.

    Thanks,

    Rupashri

  • July 31, 2009 at 7:30 am

    #1032917

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

  • July 31, 2009 at 7:33 pm

    #1033231

    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)

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

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