Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    13

    Unanswered: A question regarding SSIS

    Hi

    I have two table and i am using a merge transformation in SSIS. The table looks like:

    Table 1:

    ID TYPE STATUS FIRST NAME

    100 RAC ACTIVE JOHN

    200 QWE ACTIVE MARK

    300 QAZ ACTIVE JOEL



    Table 2:

    ID LASTNAME

    100 TAYLOR

    100 WAUGH

    200 LEE

    200 CHANG

    300 HUSSEY

    Now i am sorting the both table on ID and then using the merge transformation (not merge join). The merge transformation editor looks like:

    Merge input 1 Merge Input 2

    ID (SORT KEY:1) ID(SORT KEY:2)

    LASTNAME TYPE

    <IGNORE> STATUS

    <IGNORE> FIRSTNAME

    There are <ignore> in the merge input 1 because there are no more columns to map( Table 2 has two columns ans Table 1 has four columns)

    and the output in the FLAT FILE looks like :

    100, RAC, ACTIVE, JOHN

    100, TAYLOR, ,

    100, WAUGH, ,

    200, QWE, ACTIVE, MARK

    200, LEE, ,

    200, CHANG, ,

    300, QAZ, ACTIVE, JOEL

    300, HUSSEY, ,

    NOW FINALLY MY CONCERN IS HOW TO REMOVE THE COMMAS(,) HANGING AT THE BACK OF LASTNAME.

    They are hanging there because in the mapping there were some <ignore> columns. so they are nulls. I dont want any commas at the back.

    Is there any way we can take off these commas.

    Thanks

    Harry

  2. #2
    Join Date
    Oct 2011
    Posts
    9

    Wink Remove field separator

    In flat file we must use field separator to let ETLs knows that we reach the end of the field and record separator to end of record.
    the comas appear as field separator and (\n) new line as record separator, if you want to remove it you must change field separator by using "Tab" or "Space" instead of coma. this help you when you want to insert this data on database it will be inserted without problem.

    I think this solution will not harm your data.




    Elmozamil
    Last edited by Pat Phelan; 12-27-11 at 19:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •