Post reply

How to remove blank values and load data into one row

  • January 11, 2009 at 11:36 pm

    #217419

    Hi SSIS gurus,

    I am using ssis to load data into sql server. i have a flat file source.in that flatfile source data is like

    flat file Source

    --------------

    AccNo,Firstname, lastname, address1,address2,address3

    1101, Kumara, Reddy, 13

    1101, kumara, Reddy, N.M.Road

    1101, Kumara, Reddy, UK

    i want to load into sql server like that

    target

    -------

    AccNo,Firstname, lastname, address1,address2,address3

    1101, Kumara, Reddy, 13, N.M.Road, Uk

    How to solve this problem which transformation i have to use fix this.what r the exression i have to write

    tell me clearly

    appriciated u r help

    Thanks

    murali

  • January 12, 2009 at 3:16 am

    #925153

    Is this a homework question from a college course, by any chance?

    This thread looks too similar to be a coincidence:

    http://www.sqlservercentral.com/Forums/Topic634303-148-1.aspx

    Phil

    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

  • January 12, 2009 at 7:22 am

    #925252

    Phil Parkin (1/12/2009)

    Is this a homework question from a college course, by any chance?

    This thread looks too similar to be a coincidence:

    http://www.sqlservercentral.com/Forums/Topic634303-148-1.aspx

    Phil

    Hey Phil, you might want to post this in the other thread too. They're way too similar for this to be a coincidence.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 13, 2009 at 2:47 am

    #925725

    Agreed and done.

    Phil

    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

  • January 13, 2009 at 4:02 am

    #925742

    Hi Phil

    its not coincidence.both are working same company.we sent without know each other.beacuse we are traying this for past 3 days.if i distrub sorry.if u know the answer tell me its urget

    Thanks

    murali

  • January 13, 2009 at 4:23 am

    #925750

    you may have to write a custom script for this.

    All the lines of data in the source file in the same format. eg. all three address columns are in three seperate rows ?

  • January 13, 2009 at 5:04 am

    #925761

    Thank u Steveb

    if u dont mind can u write the related script otherwise tell me the related link.because i dont know coding part.

    Thanks

    Murali

  • January 13, 2009 at 5:10 am

    #925763

    You might find this a lot easier to import into a staging table in the format you have, then selecting from there.

    The query would look something like

    SELECT AccNo, Firstname, lastname,

    MAX(address1) AS address1,

    MAX(address2) AS address2,

    MAX(address3) AS address3

    -- INTO AnotherTable

    FROM YourStagingTable

    GROUP BY AccNo, Firstname, lastname

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 13, 2009 at 6:09 am

    #925791

    muralikrishna37 (1/13/2009)

    Thank u Steveb

    if u dont mind can u write the related script otherwise tell me the related link.because i dont know coding part.

    Thanks

    Murali

    It will be hard to write a script without seeing some more data as I am not sure that all the addresses will be in the same format, If they are then go with Chris' suggestion.

  • January 13, 2009 at 6:33 am

    #925807

    Thanks very much Chris Morris and steveb

    murali

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

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