Hi ALL I want to transfer the data by joining two tables in SSIS and put the merged data in a flat file. Its easy but i want a normalized data in the flat file. The following example will give a clear picture: Employee Table ID FIRST NAME LAST NAME 100 SINGH HARRY 200 MARTY JOSE 300 JOHN REE Dept table ID DEPT 100 IT 100 ENGLISH 200 SCIENCE 200 PHYSICS 300 MATHS 300 CHEMISTRY I joined the two table with merge join transformation in SSIS and i am expecting a result like: ID FIRST NAME LAST NAME DEPARTMENT 100 HARRY SINGH IT ENGLISH 200 MARTY JOSE SCIENCE PHYSICS 300 JOHN REE MATHS CHEMISTRY Can we produce such file in SSIS where the names are not repeated. I am doing a merge join (inner join) but dont want the redundant data. Please advice.

You can generate such a file using SSIS, but you'll need to use a Script component. (There are other ways, but this is the most straightforward - if you don't know .Net at all, let me know.) Place a Script component after your Merge Join, as a transformation. Mark the ID, FIRST NAME, and LAST NAME columns as ReadWrite. Edit the script code. Create a class-level variable for the ID (should be a string - if it's an integer column you need to convert it to a string first). In the PreExecute method body, set the variable to a zero-length string. In the ProcessInputRow method body, compare your variable to the Row.ID value. If they're different, place the Row.ID value into your variable. If they're the same, set the ID, FIRST NAME, and LAST NAME columns to zero-length strings. Talk to me now on

Hi Todd Thank you so much for replying. Could you please help me with the coding as well. I will really appreciate your help. Thanks once again Harry

Hi Todd I am sorry to Bug you again. Actually, i want to have a following type of flat file instead of what i mentioned before. Could you please help me to generate such a file. Thanks Todd Harry 100 HARRY SINGH 100 IT 100 ENGLISH 200 MARTY JOSE 200 SCIENCE 200 PHYSICS 300 JOHN REE 300 MATHS 300 CHEMISTRY

Hi Harry , After Merge Join use Derived Column I hope this Link will resolve all your problem. http://social.msdn.microsoft.com/Forums/sr-Latn-CS/sqlintegrationservices/thread/78f4c6d0-acb4-4ef4-b899-901186bc793e Regards Ajay Singh Rawat

Two Flat file source Sort by ID Use MERGE instead of MERGE join In the Merge set up Ignore lastname column for DEPT feed Let me know if this helped?

Hi shashikanta Can you please let me know how to do the same task by using two oledb source instead of flat files. I really appreciate your help. Harry

Hi shashikanta Can you please let me know how to do the same task by using two oledb source instead of flat files. I really appreciate your help. Harry

It doesn't matter what the sources are. Talk to me now on

Hi Shashikanta I was able to get the same result by using two oledb source. thanks for your guidance Now my concern is that i want the name at the top and then data from another table like: 100 HARRY SINGH 100 IT 100 ENGLISH 200 MARTY JOSE 200 SCIENCE 200 PHYSICS 300 JOHN REE 300 MATHS 300 CHEMISTRY Thanks Harry

Hi Shashikanta I was able to get the same result by using two oledb source. thanks for your guidance Now my concern is that i want the name at the top and then data from another table like: 100 HARRY SINGH 100 IT 100 ENGLISH 200 MARTY JOSE 200 SCIENCE 200 PHYSICS 300 JOHN REE 300 MATHS 300 CHEMISTRY Thanks Harry

In order to do that, insert a Derived Column immediately after EACH of your OLE DB Sources. In the Derived Column following the EMP table, add a new "order" column with a value of 1. In the Derived Column following add a new "order" column with a value of 2. In each of the Sort components, add the "order" column to the sort key. Now the Merge will keep the employee names before the department details. Talk to me now on

In order to do that, insert a Derived Column immediately after EACH of your OLE DB Sources. In the Derived Column following the EMP table, add a new "order" column with a value of 1. In the Derived Column following add a new "order" column with a value of 2. In each of the Sort components, add the "order" column to the sort key. Now the Merge will keep the employee names before the department details. Talk to me now on

Hi Todd I did what you suggested and i got the following flat file: 100,harry,singh 200,marty,jose 300,john,ree 100,maths, 100,english, 200,litreature, 200,science, 200,physics, 300,chemistry, 300,sanskrit, All the emp details coming first and then all the dept details. I was expecting first emp detail and then his/her dept detail, then second emp detail and then his/her dept detail and so on like the follow: 100 HARRY SINGH 100 IT 100 ENGLISH 200 MARTY JOSE 200 SCIENCE 200 PHYSICS 300 JOHN REE 300 MATHS 300 CHEMISTRY Please advice on this matter. Thanks Harry

Hi Todd I did what you suggested and i got the following flat file: 100,harry,singh 200,marty,jose 300,john,ree 100,maths, 100,english, 200,litreature, 200,science, 200,physics, 300,chemistry, 300,sanskrit, All the emp details coming first and then all the dept details. I was expecting first emp detail and then his/her dept detail, then second emp detail and then his/her dept detail and so on like the follow: 100 HARRY SINGH 100 IT 100 ENGLISH 200 MARTY JOSE 200 SCIENCE 200 PHYSICS 300 JOHN REE 300 MATHS 300 CHEMISTRY Please advice on this matter. Thanks Harry

Hi Harry, In the destination file connection you check Header at First row. Create a destination file with required headers . -- Shashi

Hi Harry, In the destination file connection you check Header at First row. Create a destination file with required headers . -- Shashi

That's because you replaced your sort key with just the "order" sort. Don't do that. Sort by your ID, then by order. Talk to me now on

That's because you replaced your sort key with just the "order" sort. Don't do that. Sort by your ID, then by order. Talk to me now on

That's because you replaced your sort key with just the "order" sort. Don't do that. Sort by your ID, then by order. Talk to me now on