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

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