August 3, 2011 at 3:28 pm
hi, can someone tell me if this is possible in SSIS (or SSMS)? i have 2 files which i would like to merger together based on a common ID field:
file1
ID, Name, Address
100,Smith,123 Any Street
file2
ID,Tran1,Tran2
100,ABC,XYZ
100,DEF,GHI
i would like the output file (file3) to read as follows:
ID,Name,Address,Tran1,Tran2,Tran3,Tran4
100,Smith,123 Any Street,ABC,XYZ,DEF,GHI
the final output file combines the name and address and all the transactions for that person
any help is greatly appreciated
thanks
August 3, 2011 at 3:52 pm
Will your output file always have the same number of columns? To the best of my knowledge SSIS can not handle a dynamic number of columns.
I am not sure if the requirement of your output file can be changed but a more 'normalized' way to output this data would be:
ID,Name,Address,Tran
100,Smith,123 Any Street,ABC
100,Smith,123 Any Street,XYZ
100,Smith,123 Any Street,DEF
100,Smith,123 Any Street,GHI
If someone was sending me this data that's they way I would prefer it.
August 3, 2011 at 5:32 pm
yes, the output will always have 27 columns. i was able to output the file in the format you suggested but unfortunately that's not what the client wants. thanks for your response, think i'll be able to accomplish this, either in SSIS or SSMS?
August 3, 2011 at 5:44 pm
If the column count will be static then use I believe you could use SSIS.
So you will import your two files into two separate tables.
The trick will be to combine the two tables into your desired output. The below link shows a similar situation.
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
I have done something similar a long time ago without XML PATH but it now looks like XML PATH is the preferred method.
Your query once written will be the source in your export package.
If you need further help with the query provide table definitions, sample data and an example of your desired output and someone here should be able to help.
August 3, 2011 at 10:01 pm
thank you very much. i looked at the link you provided and i'm sure i can do it now. thanks again
August 4, 2011 at 10:16 am
:ermm: XML PATH helps create a delimited list as a single column. The example in the OP has a separate column for each transaction.
@krish2266, did you get your issue resolved? If so please let us know how. It may help others looking onto this thread from Google or other sources.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply